On Thu, May 6, 2010 at 10:38 AM, Tom Lane t...@sss.pgh.pa.us wrote:
* the index grows as the size of the total data set, it's not limited
by partition size
* can't cheaply drop one partition any more, you have to vacuum the
(big) index first
So I wholeheartedly agree with the general
On May 6, 2010, at 4:31 AM, Florian Pflug wrote:
The use case for this was there were different news items,
and there were another table for summaries, that could point
to any of the news items table. Another use case could be
a large partitioned table with an FK to the main table where
the
The referential integrity triggers contain some extra magic that isn't
easily simulatable in userland, and that is necessary to make the
foreign key constraints airtight. We've discussed this previously but
I don't remember which thread it was or the details of when things
blow up. I think
On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov fo...@ac-sw.com wrote:
The referential integrity triggers contain some extra magic that isn't
easily simulatable in userland, and that is necessary to make the
foreign key constraints airtight. We've discussed this previously but
I don't
On 2010-05-11 14:29 +0200, Robert Haas wrote:
On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov fo...@ac-sw.com wrote:
The referential integrity triggers contain some extra magic that isn't
easily simulatable in userland, and that is necessary to make the
foreign key constraints airtight. We've
2010/5/11 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi:
On 2010-05-11 14:29 +0200, Robert Haas wrote:
On Tue, May 11, 2010 at 2:16 AM, Dmitry Fefelov fo...@ac-sw.com wrote:
The referential integrity triggers contain some extra magic that isn't
easily simulatable in userland, and that is
This is getting way off topic, but:
On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote:
T2 SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR SHARE.
i
---
1
(1 Zeile)
T2 SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got
anything pointing to it.
a_id
--
(0
2010/5/11 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi:
This is getting way off topic, but:
On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote:
T2 SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR
SHARE.
i
---
1
(1 Zeile)
T2 SELECT a_id FROM b WHERE a_id = 1; -- Check
On 5/11/10 4:07 PM +0300, Nicolas Barbier wrote:
2010/5/11 Marko Tiikkajamarko.tiikk...@cs.helsinki.fi:
This is getting way off topic, but:
On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote:
T2SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR
SHARE.
i
---
1
(1 Zeile)
T2
On 5/11/10 4:11 PM +0300, I wrote:
I took the SELECT ... FOR SHARE suggestion in a more general way,
suggesting the use of row-level locks. T2 should be holding an
exclusive row-level lock (SELECT ... FOR UPDATE) when checking for
references.
Hmm. Right, that transaction wouldn't see the
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes:
On 5/11/10 4:11 PM +0300, I wrote:
I took the SELECT ... FOR SHARE suggestion in a more general way,
suggesting the use of row-level locks. T2 should be holding an
exclusive row-level lock (SELECT ... FOR UPDATE) when checking for
Nicolas Barbier nicolas.barb...@gmail.com wrote:
Switch to T1:
1 COMMIT; -- Commit the insertion...
COMMIT
T2 continues:
DELETE 1
T2 COMMIT; -- Commit the deletion of a with i = 1.
COMMIT
T2 SELECT * FROM b EXCEPT SELECT * FROM a;
a_id
--
1
(1 Zeile)
Woops.
This
On May 6, 2010, at 10:52 , Boszormenyi Zoltan wrote:
=# create table parent (id serial primary key, t text);
...
=# create table child () inherits (parent);
...
=# create table refer (id serial primary key, parent_id integer
...
=# insert into child (t) values ('a') returning id;
...
=#
2010/5/6 Boszormenyi Zoltan z...@cybertec.at:
=# insert into refer (parent_id) values (1);
ERROR: insert or update on table refer violates foreign key
constraint refer_parent_id_fkey
DETAIL: Key (parent_id)=(1) is not present in table parent.
The use case for this was there were different
On Thu, May 6, 2010 at 6:37 AM, Jaime Casanova ja...@2ndquadrant.com wrote:
i would call it a bug, but this is a known issue
The only solution currently is that the referring table has to be
partitioned the same way as the referred table in the FK, and
its parent table has to be queried.
Florian Pflug f...@phlo.org writes:
What lies at the heart of this problem is the lack of multi-table
indices and hence multi-table unique constraints in postgres. AFAIK
with those in place the rest amounts to the removal of ONLY from the
constraint check queries plus some code to propagate
On May 6, 2010, at 16:38 , Tom Lane wrote:
Florian Pflug f...@phlo.org writes:
What lies at the heart of this problem is the lack of multi-table
indices and hence multi-table unique constraints in postgres. AFAIK
with those in place the rest amounts to the removal of ONLY from the
constraint
Florian Pflug f...@phlo.org writes:
Anyway, I was wondering why we need guaranteed uniqueness for FK
relationships anyway.
It's required by spec, and the semantics aren't terribly sensible
without it.
regards, tom lane
--
Sent via pgsql-hackers mailing list
18 matches
Mail list logo
