I'd like to resurrect a subset of my proposal in [1], specifically that:
The FOREIGN KEY constraint syntax gains a [ USING INDEX index_name ] clause
optionally following the referenced column list.
The index specified by this clause is used to support the foreign key
constraint, and it mu
On Fri, May 26, 2023 at 12:49 PM Tom Lane wrote:
> > Just to clarify, there's no way for SELECT FROM foo WHERE ctid = NEW.ctid
> > to return a row that ordinary wouldn't be visible right? There's no magic
> > going on with the qual on ctid that skips a visibility check right?
>
> No, a ctid test
On Fri, May 26, 2023 at 11:34 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Fri, May 26, 2023 at 8:04 AM Kaiting Chen wrote:
>
>> I need to implement a trigger that will behave similarly to a foreign key
>> constraint. The trigger itself will be crea
I need to implement a trigger that will behave similarly to a foreign key
constraint. The trigger itself will be created with:
CREATE CONSTRAINT TRIGGER ... AFTER INSERT OR UPDATE OF ... ON foo
I'd like to skip execution of the trigger logic if, by the time that the
trigger
is executed, the NEW
> For one example of where the semantics get fuzzy, it's not
> very clear how the extra-baggage columns ought to participate in
> CASCADE updates. Currently, if we have
>CREATE TABLE foo (a integer PRIMARY KEY, b integer);
> then an update that changes only foo.b doesn't need to update
> refer
> So the broader point I'm trying to make is that, as I understand it,
> indexes backing foreign key constraints is an implementation detail.
> The SQL standard details the behavior of foreign key constraints
> regardless of implementation details like a backing index. That means
> that the behavio
>>> Another example is that I think the idea is only well-defined when
>>> the subset column(s) are a primary key, or at least all marked NOT NULL.
>>> Otherwise they're not as unique as you're claiming. But then the FK
>>> constraint really has to be dependent on a PK constraint not just an
>>> i
> Could we create this additional unique constraint implicitly, when using
> FOREIGN KEY ... REFERENCES on a superset of unique columns? This would
> make it easier to use, but still give proper information_schema output.
Currently, a foreign key declared where the referenced columns have only a
u
> As I was reading through the email chain I had this thought: could you
> get the same benefit (or 90% of it anyway) by instead allowing the
> creation of a uniqueness constraint that contains more columns than
> the index backing it? So long as the index backing it still guaranteed
> the uniquene
> For one example of where the semantics get fuzzy, it's not
> very clear how the extra-baggage columns ought to participate in
> CASCADE updates. Currently, if we have
>CREATE TABLE foo (a integer PRIMARY KEY, b integer);
> then an update that changes only foo.b doesn't need to update
> refer
On Fri, Jun 10, 2022 at 12:14 AM Peter Eisentraut
wrote:
>
> On 10.06.22 05:47, David Rowley wrote:
> >> I think this should be referring to constraint name, not an index name.
> > Can you explain why you think that?
>
> If you wanted to specify this feature in the SQL standard (I'm not
> proposin
Hi everyone:
I'd like to propose a change to PostgreSQL to allow the creation of a foreign
key constraint referencing a superset of uniquely constrained columns.
As it currently stands:
CREATE TABLE foo (a integer PRIMARY KEY, b integer);
CREATE TABLE bar (x integer, y integer,
FOREIGN K
Applied cleanly for me. Here are my performance test results:
count
-
300
(1 row)
Time: 3167.836 ms (00:03.168)
count
--
1010
(1 row)
Time: 6074.369 ms (00:06.074)
count
---
10001
(1 row)
Time: 8.159 ms
The performance improves substantially in case 2 as adv
13 matches
Mail list logo