Tag: constraint

Waiting for 9.5 – Reduce lock levels of some trigger DDL and add FKs

On 5th of April, Simon Riggs committed patch:

Reduce lock levels of some trigger DDL and add FKs
 
Reduce lock levels to ShareRowExclusive for the following SQL
 CREATE TRIGGER (but not DROP or ALTER)
 ALTER TABLE ENABLE TRIGGER
 ALTER TABLE DISABLE TRIGGER
 ALTER TABLE … ADD CONSTRAINT FOREIGN KEY
 
Original work by Simon Riggs, extracted and refreshed by Andreas Karlsson
New test cases added by Andreas Karlsson
Reviewed by Noah Misch, Andres Freund, Michael Paquier and Simon Riggs

Continue reading Waiting for 9.5 – Reduce lock levels of some trigger DDL and add FKs

Waiting for 9.4 – ALTER TABLE … ALTER CONSTRAINT for FKs

On 28th of June, Simon Riggs committed patch:

ALTER TABLE ... ALTER CONSTRAINT for FKs
 
Allow constraint attributes to be altered,
so the default setting of NOT DEFERRABLE
can be altered to DEFERRABLE and back.
 
Review by Abhijit Menon-Sen

Continue reading Waiting for 9.4 – ALTER TABLE … ALTER CONSTRAINT for FKs

Waiting for 9.3 – Provide database object names as separate fields in error messages.

Yet another missed thing for “Waiting for 9.3". Sorry about that.

On 29th of January, Tom Lane committed patch:

Provide database object names as separate fields in error messages.
 
This patch addresses the problem that applications currently have to
extract object names from possibly-localized textual error messages,
if they want to know for example which index caused a UNIQUE_VIOLATION
failure.  It adds new error message fields to the wire protocol, which
can carry the name of a table, table column, data type, or constraint
associated with the error.  (Since the protocol spec has always instructed
clients to ignore unrecognized field types, this should not create any
compatibility problem.)
 
Support for providing these new fields has been added to just a limited set
of error reports (mainly, those in the "integrity constraint violation"
SQLSTATE class), but we will doubtless add them to more calls in future.
 
Pavel Stehule, reviewed and extensively revised by Peter Geoghegan, with
additional hacking by Tom Lane.

Continue reading Waiting for 9.3 – Provide database object names as separate fields in error messages.

Waiting for 9.2 – NOT VALID CHECKS

On 30th of June, Alvaro Herrera committed patch:

Enable CHECK constraints to be declared NOT VALID
 
This means that they can initially be added to a large existing table
without checking its initial contents, but new tuples must comply to
them; a separate pass invoked by ALTER TABLE / VALIDATE can verify
existing data and ensure it complies with the constraint, at which point
it is marked validated and becomes a normal part of the table ecosystem.
 
An non-validated CHECK constraint is ignored in the planner for
constraint_exclusion purposes; when validated, cached plans are
recomputed so that partitioning starts working right away.
 
This patch also enables domains to have unvalidated CHECK constraints
attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT
VALID, which can later be validated with ALTER DOMAIN / VALIDATE
CONSTRAINT.
 
Thanks to Thom Brown, Dean Rasheed and Jaime Casanova for the various
reviews, and Robert Hass for documentation wording improvement
suggestions.
 
This patch was sponsored by Enova Financial.

Continue reading Waiting for 9.2 – NOT VALID CHECKS

Waiting for 9.1 – INVALID FOREIGN KEYS

On 8th of February, Simon Riggs committed patch:

Extend ALTER TABLE TO allow FOREIGN KEYS TO be added WITHOUT initial validation.
FK constraints that are marked NOT VALID may later be VALIDATED, which uses an
ShareUpdateExclusiveLock ON CONSTRAINT TABLE AND RowShareLock ON referenced
TABLE. Significantly reduces LOCK strength AND duration WHEN adding FKs.
NEW state visible FROM psql.
 
Simon Riggs, WITH reviews FROM Marko Tiikkaja AND Robert Haas

Continue reading Waiting for 9.1 – INVALID FOREIGN KEYS

Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index

On 25th of January, Tom Lane committed patch:

Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX.
 
This feature allows a UNIQUE OR pkey CONSTRAINT TO be created USING an
already-existing UNIQUE INDEX.  While the CONSTRAINT isn't very
functionally different from the bare index, it's nice TO be able TO do that
FOR documentation purposes.  The main advantage OVER just issuing a plain
ALTER TABLE ADD UNIQUE/PRIMARY KEY IS that the INDEX can be created WITH
CREATE INDEX CONCURRENTLY, so that there IS NOT a long INTERVAL WHERE the
TABLE IS locked against updates.
 
ON the way, refactor SOME OF the code IN DefineIndex() AND index_create()
so that we don't have to pass through those functions in order to create
the index constraint's catalog entries.  Also, IN parse_utilcmd.c, pass
around the ParseState pointer IN struct CreateStmtContext TO save ON
notation, AND ADD error location pointers TO SOME error reports that didn't
have one before.
 
Gurjeet Singh, reviewed by Steve Singer and Tom Lane

Continue reading Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index

CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03

UPDATE

Please read also about this change in Pg 9.1, and this change in Pg 9.2 posts, as they explain that since Pg 9.1 some of the limitations listed in this post are no longer there.

END OF UPDATE

Fight!

But more seriously – people tend to use various data types, and there have been some myths about them, so let's see how it really boils down.

First of all – All those data types are internally saved using the same C data structure – varlena.

Thanks to this we can be nearly sure that there are no performance differences. Are there no performance differences in reality? Let's test.

Continue reading CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT – UPDATED 2010-03-03

Waiting for 8.5 – exclusion constraints

On 7th of December, Tom Lane committed patch by Jeff Davis that adds general exclusion constraints:

Log Message:
-----------
Add exclusion constraints, which generalize the concept of uniqueness to
support any indexable commutative operator, not just equality.  Two rows
violate the exclusion constraint if "row1.col OP row2.col" is TRUE for
each of the columns in the constraint.
 
Jeff Davis, reviewed by Robert Haas

Continue reading Waiting for 8.5 – exclusion constraints

Waiting for 8.5 – conditional triggers

On 20th of November Tom Lane committed patch by Takahiro Itagaki which adds interesting functionality:

ADD a WHEN clause TO CREATE TRIGGER, allowing a BOOLEAN expression TO be
checked TO determine whether the TRIGGER should be fired.
 
FOR BEFORE triggers this IS mostly a matter OF spec compliance; but FOR AFTER
triggers it can provide a noticeable performance improvement, since queuing OF
a DEFERRED TRIGGER event AND re-fetching OF the ROW(s) at END OF statement can
be short-circuited IF the TRIGGER does NOT need TO be fired.
 
Takahiro Itagaki, reviewed BY KaiGai Kohei.

Continue reading Waiting for 8.5 – conditional triggers