agora inbox for [email protected]
help / color / mirror / Atom feedForeign Keys
25+ messages / 22 participants
[nested] [flat]
* Foreign Keys
@ 1998-03-24 04:04 PostgreSQL On Peanuts.roanoke.edu <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: PostgreSQL On Peanuts.roanoke.edu @ 1998-03-24 04:04 UTC (permalink / raw)
To: PostgreSQL HACKERS List <[email protected]>
Hello,
Have foreign keys been implemented yet?
-brian rickabaugh
^ permalink raw reply [nested|flat] 25+ messages in thread
* Foreign Keys
@ 1999-02-25 15:32 Michael Davis <[email protected]>
1999-02-25 15:37 ` Re: [INTERFACES] Foreign Keys Byron Nikolaidis <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Michael Davis @ 1999-02-25 15:32 UTC (permalink / raw)
To: '[email protected]' <[email protected]>; pgsql postgres <[email protected]>; pgsql-hackers
What is the possibility of recording foreign keys (a partial foreign key
implementation) in the database such that the ODBC interface can correctly
report them to client applications? This would require an enhancement to
Postgres and to the ODBC driver for Postgres. I have a problem with Access
97 not working properly with forms that contain sub forms representing
parent/child or master/detail tables. For example: orders and orderlines.
I think this is caused by the lack of foreign key support. I am basing this
conclusion on the SQL statement that Access sent through ODBC when looking
up records for the sub form. The where clause of the SQL statement was
based on the primary key of the child or detail table. The where clause
needs to be based on the column that is linked to the parent or master
table. I already have indexes installed on both the primary key and the
field that links to the master/parent table.
I am willing to explore a work around and/or help with these enhancements.
This issue, at least for the moment, appears to be a show stopper for me.
Any Suggestions?
Thanks, Michael
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [INTERFACES] Foreign Keys
1999-02-25 15:32 Foreign Keys Michael Davis <[email protected]>
@ 1999-02-25 15:37 ` Byron Nikolaidis <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: Byron Nikolaidis @ 1999-02-25 15:37 UTC (permalink / raw)
To: Michael Davis <[email protected]>; +Cc: '[email protected]' <[email protected]>; pgsql postgres <[email protected]>; pgsql-hackers
Michael Davis wrote:
> What is the possibility of recording foreign keys (a partial foreign key
> implementation) in the database such that the ODBC interface can correctly
> report them to client applications? This would require an enhancement to
> Postgres and to the ODBC driver for Postgres. I have a problem with Access
> 97 not working properly with forms that contain sub forms representing
> parent/child or master/detail tables. For example: orders and orderlines.
> I think this is caused by the lack of foreign key support. I am basing this
>
The driver does support SQLForeignKeys. It does say it supports this function
in SQLFunctions.
However, the current implementation is based on the SPI/trigger stuff from the
pg_trigger table. If you don't have any trigger info, then SQLForeignKeys will
not report anything. This is most likely the problem.
Byron
^ permalink raw reply [nested|flat] 25+ messages in thread
* RE: [INTERFACES] Foreign Keys
@ 1999-02-25 16:45 Michael Davis <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: Michael Davis @ 1999-02-25 16:45 UTC (permalink / raw)
To: '[email protected]' <[email protected]>; pgsql postgres <[email protected]>; pgsql-hackers
Excellent! I will start working on it.
-----Original Message-----
From: Byron Nikolaidis [SMTP:[email protected]]
Sent: Thursday, February 25, 1999 8:38 AM
To: Michael Davis
Cc: '[email protected]'; pgsql postgres;
[email protected]
Subject: Re: [INTERFACES] Foreign Keys
Michael Davis wrote:
> What is the possibility of recording foreign keys (a partial
foreign key
> implementation) in the database such that the ODBC interface can
correctly
> report them to client applications? This would require an
enhancement to
> Postgres and to the ODBC driver for Postgres. I have a problem
with Access
> 97 not working properly with forms that contain sub forms
representing
> parent/child or master/detail tables. For example: orders and
orderlines.
> I think this is caused by the lack of foreign key support. I am
basing this
>
The driver does support SQLForeignKeys. It does say it supports
this function
in SQLFunctions.
However, the current implementation is based on the SPI/trigger
stuff from the
pg_trigger table. If you don't have any trigger info, then
SQLForeignKeys will
not report anything. This is most likely the problem.
Byron
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [INTERFACES] Foreign Keys
@ 1999-03-03 07:04 Gene Selkov Jr. <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: Gene Selkov Jr. @ 1999-03-03 07:04 UTC (permalink / raw)
To: Byron Nikolaidis <[email protected]>; [email protected]; pgsql-hackers
> The second problem is this:
>
> conn=153237224, query='SELECT "RentalOrders"."rentalorderlinesid" FROM "rentalorderlines"
> "RentalOrders" WHERE ("rentalorderid" = NULL ) '
> ERROR from backend during send_query: 'ERROR: parser: parse error at or near "null"'
> STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
>
>
> Since postgres will not recognize the syntax (where 'col' = null)... it only recognizes
> "isnull". I was hoping someone would have added the ability for the parser to handle this at
> some point (Hey Dave, maybe you could contribute something here man :-).
>
>
> Byron
>
I do not poke my nose into odbc as I have nothing to do with it, but
this parsing problem caught my attention. To me, 'isnull' and '= null'
are the same token. So I fixed the aforementioned problem like this:
1. In backend/parser.scan.l, find the line that reads:
identifier {letter}{letter_or_digit}*
and put this following macro after it:
isnull ={space}*(null|NULL)
it does not matter where before the beginning of the rules section you
will put it, but it is better to keep related things close to each other.
2. In the same file, find the line that reads:
{identifier} {
and insert the following rule before it
{isnull} {
int i;
ScanKeyword *keyword;
for(i = 0; yytext[i]; i++)
if (isascii((unsigned char)yytext[i]) &&
isupper(yytext[i]))
yytext[i] = tolower(yytext[i]);
if (i >= NAMEDATALEN)
yytext[NAMEDATALEN-1] = '\0';
keyword = ScanKeywordLookup((char*)"isnull");
return keyword->value;
}
3. run make && make install in the src directory, then stop and restart postmaster
I understand it is an ugly hack but if you are desperate to get things running ...
If you don't try to use it as NULL = 'col', you should be OK.
--Gene
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [INTERFACES] Foreign Keys
@ 1999-03-05 06:57 Thomas G. Lockhart <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: Thomas G. Lockhart @ 1999-03-05 06:57 UTC (permalink / raw)
To: Gene Selkov Jr. <[email protected]>; +Cc: Postgres Hackers List <[email protected]>
> > > | NULL_P '=' a_expr
> > > { $$ = makeA_Expr(ISNULL, NULL, $3, NULL); }
> > This leads to a shift/reduce conflict in yacc.
> What's wrong with shift/reduce conflicts?
yacc looks ahead only one token to determine the parsing possibilities,
and will maintain multiple, parallel possibilities until it is able to
resolve them (keeping in mind this one-token constraint). With a
"shift/reduce" conflict, at least one path will *never* be possible,
even though you thought it should be from the grammar. So you will end
up with some language feature permanently unavailable. And worse, when a
new conflict is introduced it might be the older feature which is
trashed :(
- Tom
^ permalink raw reply [nested|flat] 25+ messages in thread
* Foreign Keys
@ 1999-06-10 09:40 Michael Meskes <[email protected]>
1999-06-10 14:25 ` Re: [HACKERS] Foreign Keys Thomas Lockhart <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Michael Meskes @ 1999-06-10 09:40 UTC (permalink / raw)
To: pgsql-hackers
What's the situation with foreign keys? Do we have them in 6.5?
Michael
--
Michael Meskes | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
Email: [email protected] | Use PostgreSQL!
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [HACKERS] Foreign Keys
1999-06-10 09:40 Foreign Keys Michael Meskes <[email protected]>
@ 1999-06-10 14:25 ` Thomas Lockhart <[email protected]>
1999-06-10 14:37 ` Re: [HACKERS] Foreign Keys Vadim Mikheev <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Thomas Lockhart @ 1999-06-10 14:25 UTC (permalink / raw)
To: Michael Meskes <[email protected]>; +Cc: pgsql-hackers
> What's the situation with foreign keys? Do we have them
> in 6.5?
Not natively. You can enforce referential integrity using procedures;
I think the contrib area has examples (but I'll bet you already knew
that).
- Thomas
--
Thomas Lockhart [email protected]
South Pasadena, California
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [HACKERS] Foreign Keys
1999-06-10 09:40 Foreign Keys Michael Meskes <[email protected]>
1999-06-10 14:25 ` Re: [HACKERS] Foreign Keys Thomas Lockhart <[email protected]>
@ 1999-06-10 14:37 ` Vadim Mikheev <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: Vadim Mikheev @ 1999-06-10 14:37 UTC (permalink / raw)
To: Thomas Lockhart <[email protected]>; +Cc: Michael Meskes <[email protected]>; pgsql-hackers
Thomas Lockhart wrote:
>
> > What's the situation with foreign keys? Do we have them
> > in 6.5?
>
> Not natively. You can enforce referential integrity using procedures;
> I think the contrib area has examples (but I'll bet you already knew
> that).
But unfortunately, in 6.5 one should use SHARE/SHARE ROW EXCLISIVE
locks when using refint.c, as described in release notes.
Vadim
^ permalink raw reply [nested|flat] 25+ messages in thread
* foreign keys?
@ 2000-01-23 02:04 Don Baccus <[email protected]>
2000-01-23 08:17 ` Re: [HACKERS] foreign keys? [email protected]
0 siblings, 1 reply; 25+ messages in thread
From: Don Baccus @ 2000-01-23 02:04 UTC (permalink / raw)
To: pgsql-hackers
I've built current sources on my brand new linux box,
thought I'd try foreign key constraints since the datamodel for
the ArsDigita Community System contains hundreds of them.
Figured this might provide a bit of a stress test for the
implementation.
So...what's wrong with the following?
donb=> create table foo(i integer);
CREATE
donb=> create table bar(i integer references foo);
ERROR: FOREIGN KEY match type UNSPECIFIED not implemented yet
donb=>
This is how I'm used to doing it in Oracle. I've tried a few
permutations, what am I missing? My copy of Date is still in
Boston...
- Don Baccus, Portland OR <[email protected]>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [HACKERS] foreign keys?
2000-01-23 02:04 foreign keys? Don Baccus <[email protected]>
@ 2000-01-23 08:17 ` [email protected]
0 siblings, 0 replies; 25+ messages in thread
From: [email protected] @ 2000-01-23 08:17 UTC (permalink / raw)
To: Don Baccus <[email protected]>; +Cc: pgsql-hackers
>I've built current sources on my brand new linux box,
>thought I'd try foreign key constraints since the datamodel for
>the ArsDigita Community System contains hundreds of them.
>Figured this might provide a bit of a stress test for the
>implementation.
>
>So...what's wrong with the following?
>
>donb=> create table foo(i integer);
>CREATE
>donb=> create table bar(i integer references foo);
>ERROR: FOREIGN KEY match type UNSPECIFIED not implemented yet
>donb=>
>
>This is how I'm used to doing it in Oracle. I've tried a few
>permutations, what am I missing? My copy of Date is still in
>Boston...
As of the last snapshot I downloaded, only MATCH FULL was
implemented fully and I think postgres will only be happy
if you specify the column list or table foo has a primary key.
create table bar(i integer references foo(i) match full);
That seems to work, although by the way I read the spec, I'm
not sure that it should since there is no unique constraint
specified on foo(i).
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: [HACKERS] foreign keys?
@ 2000-01-23 15:35 Don Baccus <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: Don Baccus @ 2000-01-23 15:35 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-hackers
At 03:17 AM 1/23/00 -0500, [email protected] wrote:
>As of the last snapshot I downloaded, only MATCH FULL was
>implemented fully and I think postgres will only be happy
>if you specify the column list or table foo has a primary key.
I actually tried creating table foo with and without a primary
key, with no effect.
>create table bar(i integer references foo(i) match full);
>
>That seems to work, although by the way I read the spec, I'm
>not sure that it should since there is no unique constraint
>specified on foo(i).
Good point, I wonder? Maybe I should get my girlfriend to
snail-mail me my copy of Date, since I won't be back to Boston
until March.
Anyway, the following works:
create table foo(i integer primary key);
create table bar(i integer references foo match full);
It finds the primary key within foo to match upon.
Thanks for the help.
- Don Baccus, Portland OR <[email protected]>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Foreign keys
@ 2006-09-09 22:55 MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:43 ` Re: Foreign keys Stefan Kaltenbrunner <[email protected]>
0 siblings, 2 replies; 25+ messages in thread
From: MAR - Secretariado Geral @ 2006-09-09 22:55 UTC (permalink / raw)
To: pgsql-hackers
Hi everybody,
First of all i'de like to apolagize cause my poor english. After this, i shuould say that i beleavee a year ago i brought this problem to the community but i donn't remember some answering about it. The problem is:
Every time a users misses a external refrenced key the PGSql raises an exception.
Well as far as i realise if we had 5 or 10 Foreign keys during an Insert/Update transaction only exception should be raised reporting all erros/messages after last external refrenced field missed at one time,not one by one.
Well, in order to implement this idea we will need to desable the built-in refencial integrety and build it all by your self- all the validation (look-ups etc..) before insert/update If tg_op='insert' or tg_op='update' then as people do with non relational Databases - all hand-made. Well, this is very hard to beleave!!! I must be missing something.
Please i'must be wrong can some one explain me what i'm missing?
Thanks in Advance
Mário Reis
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
@ 2006-09-10 08:36 ` Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
1 sibling, 1 reply; 25+ messages in thread
From: Chris Mair @ 2006-09-10 08:36 UTC (permalink / raw)
To: MAR - Secretariado Geral <[email protected]>; +Cc: pgsql-hackers
> First of all i'de like to apolagize cause my poor english. After this,
> i shuould say that i beleavee a year ago i brought this problem to the
> community but i donn't remember some answering about it. The problem
> is:
>
> Every time a users misses a external refrenced key the PGSql raises an
> exception.
> Well as far as i realise if we had 5 or 10 Foreign keys
> during an Insert/Update transaction only exception should be raised
> reporting all erros/messages after last external refrenced field
> missed at one time,not one by one.
> Well, in order to implement this idea we will need to desable the
> built-in refencial integrety and build it all by your self- all the
> validation (look-ups etc..) before insert/update If tg_op='insert' or
> tg_op='update' then as people do with non relational Databases - all
> hand-made. Well, this is very hard to beleave!!! I must be missing
> something.
>
> Please i'must be wrong can some one explain me what i'm missing?
When there is a constraint violation, the current transaction is rolled
back anyhow.
What's the purpose of letting you insert 1000 records, then, at the end
say: "hah, all is rolled back becauase the 2nd record was invalid".
PG justly throws the exception immediately to let you know it's futile
inserting 998 more records.
I don't see a problem here.
Bye,
Chris.
--
Chris Mair
http://www.1006.org
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
@ 2006-09-10 08:57 ` Gregory Stark <[email protected]>
2006-09-10 14:19 ` Re: Foreign keys Stephan Szabo <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Gregory Stark @ 2006-09-10 08:57 UTC (permalink / raw)
To: Chris Mair <[email protected]>; +Cc: MAR - Secretariado Geral <[email protected]>; pgsql-hackers
Chris Mair <[email protected]> writes:
> What's the purpose of letting you insert 1000 records, then, at the end
> say: "hah, all is rolled back becauase the 2nd record was invalid".
> PG justly throws the exception immediately to let you know it's futile
> inserting 998 more records.
Well there's plenty of cases where people want that and we support it with
deferred constraints.
However the OP sounds like he wants something else. I think what he wants is
when he inserts a record and it fails due to foreign key constraints to report
all the violated constraints, not just the first one found.
I never run into this problem myself because I think of foreign key
constraints as more akin to C assertions. They're a backstop to make sure the
application is working correctly. I never write code that expects foreign key
constraint errors and tries to handle them.
But there's nothing saying that's the only approach. The feature request seems
pretty reasonable to me. I'm not sure how hard it would be with the ri
triggers as written. I'm not sure there's anywhere for triggers to store their
"return values" so I'm unclear this can even be done using triggers.
But to answer his original question: yes that's the way Postgres works and if
you want to report all the violations together you'll have to check them
yourself.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
@ 2006-09-10 14:19 ` Stephan Szabo <[email protected]>
2006-09-10 15:09 ` Re: Foreign keys Tom Lane <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Stephan Szabo @ 2006-09-10 14:19 UTC (permalink / raw)
To: Gregory Stark <[email protected]>; +Cc: Chris Mair <[email protected]>; MAR - Secretariado Geral <[email protected]>; pgsql-hackers
On Sun, 10 Sep 2006, Gregory Stark wrote:
> Chris Mair <[email protected]> writes:
>
> > What's the purpose of letting you insert 1000 records, then, at the end
> > say: "hah, all is rolled back becauase the 2nd record was invalid".
> > PG justly throws the exception immediately to let you know it's futile
> > inserting 998 more records.
>
> Well there's plenty of cases where people want that and we support it with
> deferred constraints.
>
> However the OP sounds like he wants something else. I think what he wants is
> when he inserts a record and it fails due to foreign key constraints to report
> all the violated constraints, not just the first one found.
>
> I never run into this problem myself because I think of foreign key
> constraints as more akin to C assertions. They're a backstop to make sure the
> application is working correctly. I never write code that expects foreign key
> constraint errors and tries to handle them.
>
> But there's nothing saying that's the only approach. The feature request seems
> pretty reasonable to me. I'm not sure how hard it would be with the ri
> triggers as written. I'm not sure there's anywhere for triggers to store their
> "return values" so I'm unclear this can even be done using triggers.
I think if we were going to do this that all the constraint violations for
unique, not null, check and foreign keys should be handled similarly, so
we'd probably want something more general than just a way for the ri
triggers to do this. I don't have a good idea of the right solution for
that though.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 14:19 ` Re: Foreign keys Stephan Szabo <[email protected]>
@ 2006-09-10 15:09 ` Tom Lane <[email protected]>
2006-09-10 16:23 ` Re: Foreign keys Gregory Stark <[email protected]>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2006-09-10 15:09 UTC (permalink / raw)
To: Stephan Szabo <[email protected]>; +Cc: Gregory Stark <[email protected]>; Chris Mair <[email protected]>; MAR - Secretariado Geral <[email protected]>; pgsql-hackers
Stephan Szabo <[email protected]> writes:
> I think if we were going to do this that all the constraint violations for
> unique, not null, check and foreign keys should be handled similarly, so
> we'd probably want something more general than just a way for the ri
> triggers to do this. I don't have a good idea of the right solution for
> that though.
It seems pretty unwieldy to me: it's not hard to imagine a long INSERT
throwing millions of separate foreign-key errors before it's done, for
instance. And then there's the cascading-errors problem, ie, bogus
reports that happen because some prior step failed ... not least being
your client crashing and failing to tell you anything about what
happened because it ran out of memory for the error list.
My advice is to rethink the client code that wants such a behavior.
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 14:19 ` Re: Foreign keys Stephan Szabo <[email protected]>
2006-09-10 15:09 ` Re: Foreign keys Tom Lane <[email protected]>
@ 2006-09-10 16:23 ` Gregory Stark <[email protected]>
2006-09-10 16:40 ` Re: Foreign keys Joshua D. Drake <[email protected]>
2006-09-10 16:52 ` Re: Foreign keys Stephan Szabo <[email protected]>
0 siblings, 2 replies; 25+ messages in thread
From: Gregory Stark @ 2006-09-10 16:23 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Stephan Szabo <[email protected]>; Chris Mair <[email protected]>; MAR - Secretariado Geral <[email protected]>; pgsql-hackers
Tom Lane <[email protected]> writes:
> Stephan Szabo <[email protected]> writes:
> > I think if we were going to do this that all the constraint violations for
> > unique, not null, check and foreign keys should be handled similarly, so
> > we'd probably want something more general than just a way for the ri
> > triggers to do this. I don't have a good idea of the right solution for
> > that though.
>
> It seems pretty unwieldy to me: it's not hard to imagine a long INSERT
> throwing millions of separate foreign-key errors before it's done, for
> instance. And then there's the cascading-errors problem, ie, bogus
> reports that happen because some prior step failed ... not least being
> your client crashing and failing to tell you anything about what
> happened because it ran out of memory for the error list.
>
> My advice is to rethink the client code that wants such a behavior.
Well you're still talking about the case of multiple queries deferring all
constraint checks to the end of the transaction. I'm not sure what the
original poster had in mind but that's not how I read it and it wasn't what I
was speculating about.
I was only thinking of situations like:
INSERT INTO TABLE USER (name, department, zipcode) VALUES ('Tom', 0, '00000');
We'll fail with an error like "violates foreign key constraint
user_department_fkey". It won't say anything about the zipcode also being
invalid.
I sure hate UIs that give you one error at a time so you have to keep fixing
one problem, clicking ok again, only to have yet another error pop up, rinse,
lather, repeat until you finally get all the problems sorted out.
Now I've never actually run into this because as I mention I always treated
the database constraints as assertion checks independent of the application
which usually enforces stricter conditions anyways. But I could see someone
arguing that having two independent sets of code implementing the same set of
conditions is poor.
In any case the same logic that leads to it being desirable to report all the
errors to the user in a UI and not just report them one by one also applies to
the database. I'm not sure it's the most important issue in the world, but it
does seem like a "it would be nice" feature if it reported all the errors in
the statement, not just the first one it finds.
--
greg
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 14:19 ` Re: Foreign keys Stephan Szabo <[email protected]>
2006-09-10 15:09 ` Re: Foreign keys Tom Lane <[email protected]>
2006-09-10 16:23 ` Re: Foreign keys Gregory Stark <[email protected]>
@ 2006-09-10 16:40 ` Joshua D. Drake <[email protected]>
2006-09-11 00:31 ` Re: Foreign keys Kevin Brown <[email protected]>
2006-09-16 17:17 ` Re: Foreign keys Jim C. Nasby <[email protected]>
1 sibling, 2 replies; 25+ messages in thread
From: Joshua D. Drake @ 2006-09-10 16:40 UTC (permalink / raw)
To: Gregory Stark <[email protected]>; +Cc: Tom Lane <[email protected]>; Stephan Szabo <[email protected]>; Chris Mair <[email protected]>; MAR - Secretariado Geral <[email protected]>; pgsql-hackers
> In any case the same logic that leads to it being desirable to report all the
> errors to the user in a UI and not just report them one by one also applies to
> the database. I'm not sure it's the most important issue in the world, but it
> does seem like a "it would be nice" feature if it reported all the errors in
> the statement, not just the first one it finds.
>
Seems kind of extraneous to me. I am guessing it would cause yet further
overhead with our foreign key checks.
My testing shows that the use of foreign keys on high velocity single
transaction loads, can cause easily a 50% reduction in performance. Why
add to that? What we need to be doing is finding a way to decrease the
impact of foreign key checks.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 14:19 ` Re: Foreign keys Stephan Szabo <[email protected]>
2006-09-10 15:09 ` Re: Foreign keys Tom Lane <[email protected]>
2006-09-10 16:23 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 16:40 ` Re: Foreign keys Joshua D. Drake <[email protected]>
@ 2006-09-11 00:31 ` Kevin Brown <[email protected]>
1 sibling, 0 replies; 25+ messages in thread
From: Kevin Brown @ 2006-09-11 00:31 UTC (permalink / raw)
To: pgsql-hackers
Joshua D. Drake wrote:
>
> >In any case the same logic that leads to it being desirable to report all
> >the
> >errors to the user in a UI and not just report them one by one also
> >applies to
> >the database. I'm not sure it's the most important issue in the world, but
> >it
> >does seem like a "it would be nice" feature if it reported all the errors
> >in
> >the statement, not just the first one it finds.
> >
>
> Seems kind of extraneous to me. I am guessing it would cause yet further
> overhead with our foreign key checks.
But in this case, it would be (or should be) overhead only in the case
of failure. In the case of success, all the constraints are checked
anyway -- they just succeed.
I would expect that the number of applications for which a constraint
violation is the norm and not the exception is very small.
But Tom's concern is a valid one. I expect a reasonable compromise
would be to record and show the errors for only the non-deferred
constraints in the currently executing statement, because after that
point the transaction is in an error state anyway and thus can't
continue without a rollback to a savepoint. It probably wouldn't make
sense to evaluate the deferred constraints within the erroring
statement anyway -- they're deferred, which by definition means they
don't get evaluated until commit, so evaluating them at failure time
could easily show errors that are only there because subsequent
statements never got executed.
As for the deferred constraints, it might be reasonable to show errors
only up to some limit (controlled by a GUC, perhaps), with the default
limit being 1, which is what we have now. Otherwise you run the risk
of throwing millions of errors, which is surely not desirable. The
downside to this is until you've hit the limit, you have to evaluate
*all* the deferred constraints, which could take a while, whereas the
current setup will return immediately upon encountering the first
constraint error.
> My testing shows that the use of foreign keys on high velocity single
> transaction loads, can cause easily a 50% reduction in performance. Why
> add to that? What we need to be doing is finding a way to decrease the
> impact of foreign key checks.
I definitely agree here, but this should be independent of how foreign
key failures are handled once they're detected. In other words, what
you're experiencing is the perfomance hit that comes from evaluating
the constraints, not from reporting the errors afterwards.
--
Kevin Brown [email protected]
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 14:19 ` Re: Foreign keys Stephan Szabo <[email protected]>
2006-09-10 15:09 ` Re: Foreign keys Tom Lane <[email protected]>
2006-09-10 16:23 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 16:40 ` Re: Foreign keys Joshua D. Drake <[email protected]>
@ 2006-09-16 17:17 ` Jim C. Nasby <[email protected]>
1 sibling, 0 replies; 25+ messages in thread
From: Jim C. Nasby @ 2006-09-16 17:17 UTC (permalink / raw)
To: Joshua D. Drake <[email protected]>; +Cc: Gregory Stark <[email protected]>; Tom Lane <[email protected]>; Stephan Szabo <[email protected]>; Chris Mair <[email protected]>; MAR - Secretariado Geral <[email protected]>; pgsql-hackers
On Sun, Sep 10, 2006 at 09:40:51AM -0700, Joshua D. Drake wrote:
>
> >In any case the same logic that leads to it being desirable to report all
> >the
> >errors to the user in a UI and not just report them one by one also
> >applies to
> >the database. I'm not sure it's the most important issue in the world, but
> >it
> >does seem like a "it would be nice" feature if it reported all the errors
> >in
> >the statement, not just the first one it finds.
> >
>
> Seems kind of extraneous to me. I am guessing it would cause yet further
> overhead with our foreign key checks.
>
> My testing shows that the use of foreign keys on high velocity single
> transaction loads, can cause easily a 50% reduction in performance. Why
> add to that? What we need to be doing is finding a way to decrease the
> impact of foreign key checks.
IIRC, a big chunk of that overhead is simply having triggers on the
table. I tested it once and found something like a 30% overhead for
having a trigger that did nothing on insert. Granted, that was a simple
test on a single machine, but still...
Obviously one place to look is in the trigger code to see if there's
performance gains to be had there. But something else to consider is
moving away from using a general-purpose trigger framework to impliment
RI. I suspect a dedicate code path for RI could be a lot leaner than the
general-purpose trigger code is.
--
Jim Nasby [email protected]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 14:19 ` Re: Foreign keys Stephan Szabo <[email protected]>
2006-09-10 15:09 ` Re: Foreign keys Tom Lane <[email protected]>
2006-09-10 16:23 ` Re: Foreign keys Gregory Stark <[email protected]>
@ 2006-09-10 16:52 ` Stephan Szabo <[email protected]>
1 sibling, 0 replies; 25+ messages in thread
From: Stephan Szabo @ 2006-09-10 16:52 UTC (permalink / raw)
To: Gregory Stark <[email protected]>; +Cc: Tom Lane <[email protected]>; Chris Mair <[email protected]>; MAR - Secretariado Geral <[email protected]>; pgsql-hackers
On Sun, 10 Sep 2006, Gregory Stark wrote:
> Tom Lane <[email protected]> writes:
>
> > Stephan Szabo <[email protected]> writes:
> > > I think if we were going to do this that all the constraint violations for
> > > unique, not null, check and foreign keys should be handled similarly, so
> > > we'd probably want something more general than just a way for the ri
> > > triggers to do this. I don't have a good idea of the right solution for
> > > that though.
> >
> > It seems pretty unwieldy to me: it's not hard to imagine a long INSERT
> > throwing millions of separate foreign-key errors before it's done, for
> > instance. And then there's the cascading-errors problem, ie, bogus
> > reports that happen because some prior step failed ... not least being
> > your client crashing and failing to tell you anything about what
> > happened because it ran out of memory for the error list.
> >
> > My advice is to rethink the client code that wants such a behavior.
>
> Well you're still talking about the case of multiple queries deferring all
> constraint checks to the end of the transaction.
Well, or insert ... select or update or delete. Most "deferred"
conditions can happen within one statement as well.
> In any case the same logic that leads to it being desirable to report all the
> errors to the user in a UI and not just report them one by one also applies to
> the database. I'm not sure it's the most important issue in the world, but it
> does seem like a "it would be nice" feature if it reported all the errors in
> the statement, not just the first one it finds.
SQL seems to have a notion of setting the size of the diagnostics area for
a transaction to hold a number of conditions. There are a few odd bits,
for example it's mostly unordered, but the sqlcode returned must match to
the first condition and we presumably want to make sure that if there are
any errors that we return an exception sql code not a completion one.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: Foreign keys
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
@ 2006-09-10 08:43 ` Stefan Kaltenbrunner <[email protected]>
1 sibling, 0 replies; 25+ messages in thread
From: Stefan Kaltenbrunner @ 2006-09-10 08:43 UTC (permalink / raw)
To: MAR - Secretariado Geral <[email protected]>; +Cc: pgsql-hackers
MAR - Secretariado Geral wrote:
> Hi everybody,
>
> First of all i'de like to apolagize cause my poor english. After this, i
> shuould say that i beleavee a year ago i brought this problem to the
> community but i donn't remember some answering about it. The problem is:
>
> Every time a users misses a external refrenced key the PGSql raises an
> exception.
> Well as far as i realise if we had 5 or 10 Foreign keys
> during an Insert/Update transaction only exception should be raised
> reporting all erros/messages after last external refrenced field missed
> at one time,not one by one.
> Well, in order to implement this idea we will need to desable the
> built-in refencial integrety and build it all by your self- all the
> validation (look-ups etc..) before insert/update If tg_op='insert' or
> tg_op='update' then as people do with non relational Databases - all
> hand-made. Well, this is very hard to beleave!!! I must be missing
> something.
>
> Please i'must be wrong can some one explain me what i'm missing?
I'm not sure what you are complining about exactly but maybe you want to
declare your FK as DEFERRABLE INITIALLY DEFERRED ?
That way the constraint checking happens at the end of the transaction
and not immediately
Stefan
^ permalink raw reply [nested|flat] 25+ messages in thread
* foreign keys
@ 2007-12-12 13:20 Sam Mason <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: Sam Mason @ 2007-12-12 13:20 UTC (permalink / raw)
To: pgsql-hackers
Hi,
How hard/generally useful would it be to allow the target of a foreign
key to be on a set of columns where only a subset of them actually have
a unique constraint. For example:
CREATE TABLE base (
id INTEGER NOT NULL PRIMARY KEY,
type INTEGER NOT NULL
);
CREATE TABLE type1info (
id INTEGER NOT NULL PRIMARY KEY,
type INTEGER NOT NULL CHECK (type = 1),
FOREIGN KEY (id,type) REFERENCES base (id,type)
);
It's possible to create a UNIQUE constraint on base(id,type) but it
seems redundant as the PRIMARY KEY constraint on id already ensures
uniqueness.
Somewhat independently, it would be nice to allow constant expressions
to be used on the left-hand-side of foreign key constraints. Allowing
them on the RHS seems nice for completeness, but appears completely
useless in practical terms. The second table would simply become:
CREATE TABLE type1info (
id INTEGER NOT NULL PRIMARY KEY,
FOREIGN KEY (id,1) REFERENCES base (id,type)
);
Sam
^ permalink raw reply [nested|flat] 25+ messages in thread
* [PATCH v29 07/11] Add DISTINCT support for IVM
@ 2023-05-31 10:08 Yugo Nagata <[email protected]>
0 siblings, 0 replies; 25+ messages in thread
From: Yugo Nagata @ 2023-05-31 10:08 UTC (permalink / raw)
When IMMV is created with DISTINCT, multiplicity of tuples is
counted and stored in "__ivm_count__" column, which is a hidden
column of IMMV. The value in __ivm_count__ is updated when IMMV
is maintained incrementally. A tuple in IMMV can be removed if
and only if the count becomes zero.
---
src/backend/commands/createas.c | 141 ++++++++++++++++++++------
src/backend/commands/indexcmds.c | 40 ++++++++
src/backend/commands/matview.c | 148 ++++++++++++++++++++++++++--
src/backend/commands/tablecmds.c | 9 ++
src/backend/parser/parse_relation.c | 18 +++-
src/backend/rewrite/rewriteDefine.c | 3 +-
src/include/commands/createas.h | 2 +
src/include/nodes/parsenodes.h | 1 +
8 files changed, 317 insertions(+), 45 deletions(-)
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 415f110516..076f35ee6b 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -53,6 +53,7 @@
#include "parser/parser.h"
#include "parser/parsetree.h"
#include "parser/parse_clause.h"
+#include "parser/parse_func.h"
#include "rewrite/rewriteHandler.h"
#include "storage/smgr.h"
#include "tcop/tcopprot.h"
@@ -309,6 +310,9 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
errhint("functions must be marked IMMUTABLE")));
check_ivm_restriction((Node *) query);
+
+ /* For IMMV, we need to rewrite matview query */
+ query = rewriteQueryForIMMV(query, into->colNames);
}
if (into->skipData)
@@ -413,6 +417,49 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
return address;
}
+/*
+ * rewriteQueryForIMMV -- rewrite view definition query for IMMV
+ *
+ * count(*) is added for counting distinct tuples in views.
+ */
+Query *
+rewriteQueryForIMMV(Query *query, List *colNames)
+{
+ Query *rewritten;
+
+ Node *node;
+ ParseState *pstate = make_parsestate(NULL);
+ FuncCall *fn;
+
+ rewritten = copyObject(query);
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ /*
+ * Convert DISTINCT to GROUP BY and add count(*) for counting distinct
+ * tuples in views.
+ */
+ if (rewritten->distinctClause)
+ {
+ TargetEntry *tle;
+
+ rewritten->groupClause = transformDistinctClause(NULL, &rewritten->targetList, rewritten->sortClause, false);
+
+ fn = makeFuncCall(SystemFuncName("count"), NIL, COERCE_EXPLICIT_CALL, -1);
+ fn->agg_star = true;
+
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle = makeTargetEntry((Expr *) node,
+ list_length(rewritten->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ rewritten->targetList = lappend(rewritten->targetList, tle);
+ rewritten->hasAggs = true;
+ }
+
+ return rewritten;
+}
+
/*
* GetIntoRelEFlags --- compute executor flags needed for CREATE TABLE AS
*
@@ -536,7 +583,8 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
ColumnDef *col;
char *colname;
- if (lc)
+ /* Don't override hidden columns added for IVM */
+ if (lc && !isIvmName(NameStr(attribute->attname)))
{
colname = strVal(lfirst(lc));
lc = lnext(into->colNames, lc);
@@ -940,10 +988,6 @@ check_ivm_restriction_walker(Node *node, void *context)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view")));
- if (qry->distinctClause)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("DISTINCT is not supported on incrementally maintainable materialized view")));
if (qry->hasDistinctOn)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -1090,12 +1134,18 @@ CreateIndexOnIMMV(Query *query, Relation matviewRel)
char idxname[NAMEDATALEN];
List *indexoidlist = RelationGetIndexList(matviewRel);
ListCell *indexoidscan;
- Bitmapset *key_attnos;
snprintf(idxname, sizeof(idxname), "%s_index", RelationGetRelationName(matviewRel));
index = makeNode(IndexStmt);
+ /*
+ * We consider null values not distinct to make sure that views with DISTINCT
+ * or GROUP BY don't contain multiple NULL rows when NULL is inserted to
+ * a base table concurrently.
+ */
+ index->nulls_not_distinct = true;
+
index->unique = true;
index->primary = false;
index->isconstraint = false;
@@ -1122,41 +1172,68 @@ CreateIndexOnIMMV(Query *query, Relation matviewRel)
index->concurrent = false;
index->if_not_exists = false;
- /* create index on the base tables' primary key columns */
- key_attnos = get_primary_key_attnos_from_query(query, &constraintList);
- if (key_attnos)
+ if (query->distinctClause)
{
+ /* create unique constraint on all columns */
foreach(lc, query->targetList)
{
TargetEntry *tle = (TargetEntry *) lfirst(lc);
Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
-
- if (bms_is_member(tle->resno - FirstLowInvalidHeapAttributeNumber, key_attnos))
- {
- IndexElem *iparam;
-
- iparam = makeNode(IndexElem);
- iparam->name = pstrdup(NameStr(attr->attname));
- iparam->expr = NULL;
- iparam->indexcolname = NULL;
- iparam->collation = NIL;
- iparam->opclass = NIL;
- iparam->opclassopts = NIL;
- iparam->ordering = SORTBY_DEFAULT;
- iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
- index->indexParams = lappend(index->indexParams, iparam);
- }
+ IndexElem *iparam;
+
+ iparam = makeNode(IndexElem);
+ iparam->name = pstrdup(NameStr(attr->attname));
+ iparam->expr = NULL;
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->opclassopts = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
}
}
else
{
- /* create no index, just notice that an appropriate index is necessary for efficient IVM */
- ereport(NOTICE,
- (errmsg("could not create an index on materialized view \"%s\" automatically",
- RelationGetRelationName(matviewRel)),
- errdetail("This target list does not have all the primary key columns. "),
- errhint("Create an index on the materialized view for efficient incremental maintenance.")));
- return;
+ Bitmapset *key_attnos;
+
+ /* create index on the base tables' primary key columns */
+ key_attnos = get_primary_key_attnos_from_query(query, &constraintList);
+ if (key_attnos)
+ {
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
+
+ if (bms_is_member(tle->resno - FirstLowInvalidHeapAttributeNumber, key_attnos))
+ {
+ IndexElem *iparam;
+
+ iparam = makeNode(IndexElem);
+ iparam->name = pstrdup(NameStr(attr->attname));
+ iparam->expr = NULL;
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->opclassopts = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
+ }
+ }
+ }
+ else
+ {
+ /* create no index, just notice that an appropriate index is necessary for efficient IVM */
+ ereport(NOTICE,
+ (errmsg("could not create an index on materialized view \"%s\" automatically",
+ RelationGetRelationName(matviewRel)),
+ errdetail("This target list does not have all the primary key columns, "
+ "or this view does not contain DISTINCT clause."),
+ errhint("Create an index on the materialized view for efficient incremental maintenance.")));
+ return;
+ }
}
/* If we have a compatible index, we don't need to create another. */
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index ab8b81b302..4811a1c8df 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -38,6 +38,7 @@
#include "commands/dbcommands.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
+#include "commands/matview.h"
#include "commands/progress.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
@@ -1104,6 +1105,45 @@ DefineIndex(Oid tableId,
safe_index = indexInfo->ii_Expressions == NIL &&
indexInfo->ii_Predicate == NIL;
+ /*
+ * We disallow unique indexes on IVM columns of IMMVs.
+ */
+ if (RelationIsIVM(rel) && stmt->unique)
+ {
+ for (int i = 0; i < indexInfo->ii_NumIndexKeyAttrs; i++)
+ {
+ AttrNumber attno = indexInfo->ii_IndexAttrNumbers[i];
+ if (attno > 0)
+ {
+ char *name = NameStr(TupleDescAttr(rel->rd_att, attno - 1)->attname);
+ if (name && isIvmName(name))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unique index creation on IVM columns is not supported")));
+ }
+ }
+
+ if (indexInfo->ii_Expressions)
+ {
+ Bitmapset *indexattrs = NULL;
+ int varno = -1;
+
+ pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);
+
+ while ((varno = bms_next_member(indexattrs, varno)) >= 0)
+ {
+ int attno = varno + FirstLowInvalidHeapAttributeNumber;
+ char *name = NameStr(TupleDescAttr(rel->rd_att, attno - 1)->attname);
+ if (name && isIvmName(name))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unique index creation on IVM columns is not supported")));
+ }
+
+ }
+ }
+
+
/*
* Report index creation if appropriate (delay this till after most of the
* error checks)
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 39305f3c49..aa518f20ef 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -152,11 +152,15 @@ static Query *rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *
static void apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
TupleDesc tupdesc_old, TupleDesc tupdesc_new,
- Query *query);
+ Query *query, bool use_count, char *count_colname);
static void apply_old_delta(const char *matviewname, const char *deltaname_old,
List *keys);
+static void apply_old_delta_with_count(const char *matviewname, const char *deltaname_old,
+ List *keys, const char *count_colname);
static void apply_new_delta(const char *matviewname, const char *deltaname_new,
StringInfo target_list);
+static void apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
+ List *keys, StringInfo target_list, const char* count_colname);
static char *get_matching_condition_string(List *keys);
static void generate_equal(StringInfo querybuf, Oid opttype,
const char *leftop, const char *rightop);
@@ -271,6 +275,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
Oid matviewOid;
Relation matviewRel;
Query *dataQuery;
+ Query *viewQuery;
Oid tableSpace;
Oid relowner;
Oid OIDNewHeap;
@@ -330,8 +335,13 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
"CONCURRENTLY", "WITH NO DATA")));
- dataQuery = get_matview_query(matviewRel);
+ viewQuery = get_matview_query(matviewRel);
+ /* For IMMV, we need to rewrite matview query */
+ if (!stmt->skipData && RelationIsIVM(matviewRel))
+ dataQuery = rewriteQueryForIMMV(viewQuery,NIL);
+ else
+ dataQuery = viewQuery;
/*
* Check that there is a unique index with no WHERE clause on one or more
@@ -511,8 +521,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
if (!stmt->skipData && RelationIsIVM(matviewRel) && !oldPopulated)
{
- CreateIndexOnIMMV(dataQuery, matviewRel);
- CreateIvmTriggersOnBaseTables(dataQuery, matviewOid);
+ CreateIndexOnIMMV(viewQuery, matviewRel);
+ CreateIvmTriggersOnBaseTables(viewQuery, matviewOid);
}
table_close(matviewRel, NoLock);
@@ -1512,6 +1522,13 @@ IVM_immediate_maintenance(PG_FUNCTION_ARGS)
int rte_index = lfirst_int(lc2);
TupleDesc tupdesc_old;
TupleDesc tupdesc_new;
+ bool use_count = false;
+ char *count_colname = NULL;
+
+ count_colname = pstrdup("__ivm_count__");
+
+ if (query->distinctClause)
+ use_count = true;
/* calculate delta tables */
calc_delta(table, rte_index, rewritten, dest_old, dest_new,
@@ -1524,7 +1541,8 @@ IVM_immediate_maintenance(PG_FUNCTION_ARGS)
{
/* apply the delta tables to the materialized view */
apply_delta(matviewOid, old_tuplestore, new_tuplestore,
- tupdesc_old, tupdesc_new, query);
+ tupdesc_old, tupdesc_new, query, use_count,
+ count_colname);
}
PG_CATCH();
{
@@ -1997,7 +2015,7 @@ rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *table, int rte
static void
apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
TupleDesc tupdesc_old, TupleDesc tupdesc_new,
- Query *query)
+ Query *query, bool use_count, char *count_colname)
{
StringInfoData querybuf;
StringInfoData target_list_buf;
@@ -2073,7 +2091,12 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
if (rc != SPI_OK_REL_REGISTER)
elog(ERROR, "SPI_register failed");
- apply_old_delta(matviewname, OLD_DELTA_ENRNAME, keys);
+ if (use_count)
+ /* apply old delta and get rows to be recalculated */
+ apply_old_delta_with_count(matviewname, OLD_DELTA_ENRNAME,
+ keys, count_colname);
+ else
+ apply_old_delta(matviewname, OLD_DELTA_ENRNAME, keys);
}
/* For tuple insertion */
@@ -2095,7 +2118,11 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
elog(ERROR, "SPI_register failed");
/* apply new delta */
- apply_new_delta(matviewname, NEW_DELTA_ENRNAME, &target_list_buf);
+ if (use_count)
+ apply_new_delta_with_count(matviewname, NEW_DELTA_ENRNAME,
+ keys, &target_list_buf, count_colname);
+ else
+ apply_new_delta(matviewname, NEW_DELTA_ENRNAME, &target_list_buf);
}
/* We're done maintaining the materialized view. */
@@ -2108,6 +2135,51 @@ apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *n
elog(ERROR, "SPI_finish failed");
}
+/*
+ * apply_old_delta_with_count
+ *
+ * Execute a query for applying a delta table given by deltname_old
+ * which contains tuples to be deleted from to a materialized view given by
+ * matviewname. This is used when counting is required, that is, the view
+ * has aggregate or distinct.
+ */
+static void
+apply_old_delta_with_count(const char *matviewname, const char *deltaname_old,
+ List *keys, const char *count_colname)
+{
+ StringInfoData querybuf;
+ char *match_cond;
+
+ /* build WHERE condition for searching tuples to be deleted */
+ match_cond = get_matching_condition_string(keys);
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "WITH t AS (" /* collecting tid of target tuples in the view */
+ "SELECT diff.%s, " /* count column */
+ "(diff.%s OPERATOR(pg_catalog.=) mv.%s) AS for_dlt, "
+ "mv.ctid "
+ "FROM %s AS mv, %s AS diff "
+ "WHERE %s" /* tuple matching condition */
+ "), updt AS (" /* update a tuple if this is not to be deleted */
+ "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.-) t.%s "
+ "FROM t WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND NOT for_dlt "
+ ")"
+ /* delete a tuple if this is to be deleted */
+ "DELETE FROM %s AS mv USING t "
+ "WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND for_dlt",
+ count_colname,
+ count_colname, count_colname,
+ matviewname, deltaname_old,
+ match_cond,
+ matviewname, count_colname, count_colname, count_colname,
+ matviewname);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
/*
* apply_old_delta
*
@@ -2157,6 +2229,66 @@ apply_old_delta(const char *matviewname, const char *deltaname_old,
elog(ERROR, "SPI_exec failed: %s", querybuf.data);
}
+/*
+ * apply_new_delta_with_count
+ *
+ * Execute a query for applying a delta table given by deltname_new
+ * which contains tuples to be inserted into a materialized view given by
+ * matviewname. This is used when counting is required, that is, the view
+ * has aggregate or distinct. Also, when a table in EXISTS sub queries
+ * is modified.
+ */
+static void
+apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
+ List *keys, StringInfo target_list, const char* count_colname)
+{
+ StringInfoData querybuf;
+ StringInfoData returning_keys;
+ ListCell *lc;
+ char *match_cond = "";
+
+ /* build WHERE condition for searching tuples to be updated */
+ match_cond = get_matching_condition_string(keys);
+
+ /* build string of keys list */
+ initStringInfo(&returning_keys);
+ if (keys)
+ {
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ appendStringInfo(&returning_keys, "%s", quote_qualified_identifier("mv", resname));
+ if (lnext(keys, lc))
+ appendStringInfo(&returning_keys, ", ");
+ }
+ }
+ else
+ appendStringInfo(&returning_keys, "NULL");
+
+ /* Search for matching tuples from the view and update if found or insert if not. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "WITH updt AS (" /* update a tuple if this exists in the view */
+ "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.+) diff.%s "
+ "FROM %s AS diff "
+ "WHERE %s " /* tuple matching condition */
+ "RETURNING %s" /* returning keys of updated tuples */
+ ") INSERT INTO %s (%s) " /* insert a new tuple if this doesn't exist */
+ "SELECT %s FROM %s AS diff "
+ "WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE %s);",
+ matviewname, count_colname, count_colname, count_colname,
+ deltaname_new,
+ match_cond,
+ returning_keys.data,
+ matviewname, target_list->data,
+ target_list->data, deltaname_new,
+ match_cond);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
/*
* apply_new_delta
*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 47c900445c..adbd768e0d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -53,6 +53,7 @@
#include "commands/cluster.h"
#include "commands/comment.h"
#include "commands/defrem.h"
+#include "commands/matview.h"
#include "commands/event_trigger.h"
#include "commands/policy.h"
#include "commands/sequence.h"
@@ -3673,6 +3674,14 @@ renameatt_internal(Oid myrelid,
targetrelation = relation_open(myrelid, AccessExclusiveLock);
renameatt_check(myrelid, RelationGetForm(targetrelation), recursing);
+ /*
+ * Don't rename IVM columns.
+ */
+ if (RelationIsIVM(targetrelation) && isIvmName(oldattname))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("IVM column can not be renamed")));
+
/*
* if the 'recurse' flag is set then we are supposed to rename this
* attribute in all classes that inherit from 'relname' (as well as in
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 864ea9b0d5..c257440414 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -36,6 +36,7 @@
#include "utils/rel.h"
#include "utils/syscache.h"
#include "utils/varlena.h"
+#include "commands/matview.h"
/*
@@ -97,7 +98,7 @@ static void expandTupleDesc(TupleDesc tupdesc, Alias *eref,
int count, int offset,
int rtindex, int sublevels_up,
int location, bool include_dropped,
- List **colnames, List **colvars);
+ List **colnames, List **colvars, bool is_ivm);
static int specialAttNum(const char *attname);
static bool rte_visible_if_lateral(ParseState *pstate, RangeTblEntry *rte);
static bool rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte);
@@ -1502,6 +1503,7 @@ addRangeTableEntry(ParseState *pstate,
rte->relid = RelationGetRelid(rel);
rte->relkind = rel->rd_rel->relkind;
rte->rellockmode = lockmode;
+ rte->relisivm = rel->rd_rel->relisivm;
/*
* Build the list of effective column names using user-supplied aliases
@@ -1587,6 +1589,7 @@ addRangeTableEntryForRelation(ParseState *pstate,
rte->relid = RelationGetRelid(rel);
rte->relkind = rel->rd_rel->relkind;
rte->rellockmode = lockmode;
+ rte->relisivm = rel->rd_rel->relisivm;
/*
* Build the list of effective column names using user-supplied aliases
@@ -2758,7 +2761,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
expandTupleDesc(tupdesc, rte->eref,
rtfunc->funccolcount, atts_done,
rtindex, sublevels_up, location,
- include_dropped, colnames, colvars);
+ include_dropped, colnames, colvars, false);
}
else if (functypclass == TYPEFUNC_SCALAR)
{
@@ -3026,7 +3029,7 @@ expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up,
expandTupleDesc(rel->rd_att, eref, rel->rd_att->natts, 0,
rtindex, sublevels_up,
location, include_dropped,
- colnames, colvars);
+ colnames, colvars, RelationIsIVM(rel));
relation_close(rel, AccessShareLock);
}
@@ -3043,7 +3046,7 @@ static void
expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
int rtindex, int sublevels_up,
int location, bool include_dropped,
- List **colnames, List **colvars)
+ List **colnames, List **colvars, bool is_ivm)
{
ListCell *aliascell;
int varattno;
@@ -3056,6 +3059,9 @@ expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
{
Form_pg_attribute attr = TupleDescAttr(tupdesc, varattno);
+ if (is_ivm && isIvmName(NameStr(attr->attname)) && !MatViewIncrementalMaintenanceIsEnabled())
+ continue;
+
if (attr->attisdropped)
{
if (include_dropped)
@@ -3218,6 +3224,10 @@ expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem,
Var *varnode = (Var *) lfirst(var);
TargetEntry *te;
+ /* if transform * into columnlist with IMMV, remove IVM columns */
+ if (rte->relisivm && isIvmName(label) && !MatViewIncrementalMaintenanceIsEnabled())
+ continue;
+
te = makeTargetEntry((Expr *) varnode,
(AttrNumber) pstate->p_next_resno++,
label,
diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c
index e36fc72e1e..f6dc7ba202 100644
--- a/src/backend/rewrite/rewriteDefine.c
+++ b/src/backend/rewrite/rewriteDefine.c
@@ -621,7 +621,8 @@ checkRuleResultList(List *targetList, TupleDesc resultDesc, bool isSelect,
attr->atttypmod))));
}
- if (i != resultDesc->natts)
+ /* No check for materialized views since this could have special columns for IVM */
+ if ((!isSelect || requireColumnNameMatch) && i != resultDesc->natts)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
isSelect ?
diff --git a/src/include/commands/createas.h b/src/include/commands/createas.h
index 09a64fa2e5..76a7873ebf 100644
--- a/src/include/commands/createas.h
+++ b/src/include/commands/createas.h
@@ -29,6 +29,8 @@ extern ObjectAddress ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *st
extern void CreateIvmTriggersOnBaseTables(Query *qry, Oid matviewOid);
extern void CreateIndexOnIMMV(Query *query, Relation matviewRel);
+extern Query *rewriteQueryForIMMV(Query *query, List *colNames);
+
extern int GetIntoRelEFlags(IntoClause *intoClause);
extern DestReceiver *CreateIntoRelDestReceiver(IntoClause *intoClause);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index fef4c714b8..1a2b8fa09e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1073,6 +1073,7 @@ typedef struct RangeTblEntry
int rellockmode; /* lock level that query requires on the rel */
struct TableSampleClause *tablesample; /* sampling info, or NULL */
Index perminfoindex;
+ bool relisivm;
/*
* Fields valid for a subquery RTE (else NULL):
--
2.25.1
--Multipart=_Mon__28_Aug_2023_16_05_30_+0900_b1OvQD_3A3ZMTGvj
Content-Type: text/x-diff;
name="v29-0008-Add-aggregates-support-in-IVM.patch"
Content-Disposition: attachment;
filename="v29-0008-Add-aggregates-support-in-IVM.patch"
Content-Transfer-Encoding: quoted-printable
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2023-05-31 10:08 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-03-24 04:04 Foreign Keys PostgreSQL On Peanuts.roanoke.edu <[email protected]>
1999-02-25 15:32 Foreign Keys Michael Davis <[email protected]>
1999-02-25 15:37 ` Byron Nikolaidis <[email protected]>
1999-02-25 16:45 RE: [INTERFACES] Foreign Keys Michael Davis <[email protected]>
1999-03-03 07:04 Re: [INTERFACES] Foreign Keys Gene Selkov Jr. <[email protected]>
1999-03-05 06:57 Re: [INTERFACES] Foreign Keys Thomas G. Lockhart <[email protected]>
1999-06-10 09:40 Foreign Keys Michael Meskes <[email protected]>
1999-06-10 14:25 ` Thomas Lockhart <[email protected]>
1999-06-10 14:37 ` Vadim Mikheev <[email protected]>
2000-01-23 02:04 foreign keys? Don Baccus <[email protected]>
2000-01-23 08:17 ` Re: [HACKERS] foreign keys? [email protected]
2000-01-23 15:35 Re: [HACKERS] foreign keys? Don Baccus <[email protected]>
2006-09-09 22:55 Foreign keys MAR - Secretariado Geral <[email protected]>
2006-09-10 08:36 ` Re: Foreign keys Chris Mair <[email protected]>
2006-09-10 08:57 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 14:19 ` Re: Foreign keys Stephan Szabo <[email protected]>
2006-09-10 15:09 ` Re: Foreign keys Tom Lane <[email protected]>
2006-09-10 16:23 ` Re: Foreign keys Gregory Stark <[email protected]>
2006-09-10 16:40 ` Re: Foreign keys Joshua D. Drake <[email protected]>
2006-09-11 00:31 ` Re: Foreign keys Kevin Brown <[email protected]>
2006-09-16 17:17 ` Re: Foreign keys Jim C. Nasby <[email protected]>
2006-09-10 16:52 ` Re: Foreign keys Stephan Szabo <[email protected]>
2006-09-10 08:43 ` Re: Foreign keys Stefan Kaltenbrunner <[email protected]>
2007-12-12 13:20 foreign keys Sam Mason <[email protected]>
2023-05-31 10:08 [PATCH v29 07/11] Add DISTINCT support for IVM Yugo Nagata <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox