Subject: Re: Need indexes on inherited tables?
From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <8141987@chezphil.org>
References: <1902614@chezphil.org> <1088283980.1215.19.camel@taz.oficina>
	<8141987@chezphil.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-7ayU5FzAOyEqt87kUd90"
Organization: AK y Asociados S.R.L.
Message-Id: <1088440216.910.153.camel@taz.oficina>
Mime-Version: 1.0
Date: Mon, 28 Jun 2004 13:30:16 -0300

--=-7ayU5FzAOyEqt87kUd90
Content-Type: multipart/related; type="multipart/alternative";
	boundary="=-KMpE3vvjAyPxVLJOdVFB"

--=-KMpE3vvjAyPxVLJOdVFB
Content-Type: multipart/alternative; boundary="=-NBEQyuFH2ztbKjIzC2fG"

--=-NBEQyuFH2ztbKjIzC2fG
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

you can find more information here:
http://www.postgresql.org/docs/7.4/static/sql-createtable.html (under
the parameters section). For unique constraints, the only thing I can
think of is a table check constraint (SELECT count(pk)=3D0).

I agree with you, right now there are many inconveniences with
postgresql inheritance, that's why I never use inheritance this way.=20
I usually stick to the "standard" way, for example, I create a people
table, and for every people I want to insert in my database there is one
row in this table, plus one row with the same id/pk (which is also a fk)
in a specialized table (let's say students, teachers, whatever).=20
This simplifies things, because attribute
definitions/defaults/constraints are always in one table. Maybe you get
into some other troubles, but I find them less painfull :)

On Sat, 2004-06-26 at 19:30, Phil Endecott wrote:

> I asked if derived tables use primary key indexes generated in the base t=
ables that they inherit from.
>=20
> Franco Bruno Borghesi replied:
> > [the derived table] is not inheriting any indexes, neither the
> > primary key constraint.
>=20
> OK, thanks!  I had come to the conclusion that it was not using the index=
, but I'm really surprised to hear that the primary key constraint that I d=
efine in a base table is not inherited.  Are any constraints inherited?  Wh=
at happens if I declare a single-column primary key?  What if I declare a "=
not null" constraint or a "check" constraint in a base table?
>=20
> Having to replicate the constraints and indexes for each derived table is=
 a pain - lots of error-prone typing - but there is a more serious problem:=
 how can I ensure that these keys are unique across all of the derived tabl=
es?  (i.e. if T1 and T2 inherit from B, and B's primary key is (id,xx), the=
n I want there to be at most one row in (T1 union T2) that has any value of=
 (id,xx).)
>=20
> Is this a possible area for future enhancements?
>=20
> Regards,
>=20
> --Phil.
>=20
>=20=20
>=20

--=-NBEQyuFH2ztbKjIzC2fG
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; CHARSET=3DUTF-8">
  <META NAME=3D"GENERATOR" CONTENT=3D"GtkHTML/3.0.10">
</HEAD>
<BODY>
you can find more information here: <A HREF=3D"http://www.postgresql.org/do=
cs/7.4/static/sql-createtable.html">http://www.postgresql.org/docs/7.4/stat=
ic/sql-createtable.html</A> (under the parameters section). For unique cons=
traints, the only thing I can think of is a table check constraint (SELECT =
count(pk)=3D0).<BR>
<BR>
I agree with you, right now there are many inconveniences with postgresql i=
nheritance, that's why I never use inheritance this way. <BR>
I usually stick to the &quot;standard&quot; way, for example, I create a pe=
ople table, and for every people I want to insert in my database there is o=
ne row in this table, plus one row with the same id/pk (which is also a fk)=
 in a specialized table (let's say students, teachers, whatever). <BR>
This simplifies things, because attribute definitions/defaults/constraints =
are always in one table. Maybe you get into some other troubles, but I find=
 them less painfull <IMG SRC=3D"cid:1088440168.910.151.camel@taz.oficina" A=
LIGN=3D"middle" ALT=3D":)" BORDER=3D"0"><BR>
<BR>
On Sat, 2004-06-26 at 19:30, Phil Endecott wrote:
<BLOCKQUOTE TYPE=3DCITE>
<PRE><FONT COLOR=3D"#737373"><I>I asked if derived tables use primary key i=
ndexes generated in the base tables that they inherit from.

Franco Bruno Borghesi replied:
&gt; [the derived table] is not inheriting any indexes, neither the
&gt; primary key constraint.

OK, thanks!  I had come to the conclusion that it was not using the index, =
but I'm really surprised to hear that the primary key constraint that I def=
ine in a base table is not inherited.  Are any constraints inherited?  What=
 happens if I declare a single-column primary key?  What if I declare a &qu=
ot;not null&quot; constraint or a &quot;check&quot; constraint in a base ta=
ble?

Having to replicate the constraints and indexes for each derived table is a=
 pain - lots of error-prone typing - but there is a more serious problem: h=
ow can I ensure that these keys are unique across all of the derived tables=
?  (i.e. if T1 and T2 inherit from B, and B's primary key is (id,xx), then =
I want there to be at most one row in (T1 union T2) that has any value of (=
id,xx).)

Is this a possible area for future enhancements?

Regards,

--Phil.

=20
</I></FONT></PRE>
</BLOCKQUOTE>
</BODY>
</HTML>

--=-NBEQyuFH2ztbKjIzC2fG--

--=-KMpE3vvjAyPxVLJOdVFB
Content-ID: <1088440168.910.151.camel@taz.oficina>
Content-Disposition: attachment; filename=smiley-3.png
Content-Type: image/png; name=smiley-3.png
Content-Transfer-Encoding: base64

iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAC+klEQVR42n2T
bUjVdxTHP/+H69Xda2VWlFe69rzthZJUoxeNOWoFGxEhYRRFmZSVW2u9ab2K
ejWE1qDNBkEQhS82VoiaZkVPmoWaKNM5mA+opbd771//997//T/+epHBarEP
HA6Hc84XDnwP/JcwcBS4AVgzcR04ONN7C+md+pcPCz44dPLAarZs/gg1UABu
Gkvvp7X1Iad+itE/YtUAle8TuH26sujzqq/LkJQsnOQQVmIASVJQMhehZORi
Jwc5d76FH2pf3gY2Aigzy7+eObqmtOqbXbjGGHZqCM+eQpJ9AHhWFCc5CAjW
f1KAkppc+qg3vRCol4Fw0aqcisOVW3HTE7hmBElSKD/5GFkNMhH1KDvegST7
8CwNSfZxeM88VuYrh4CwAuxqvxL6MnPuWiy9H1kNUPH9fZofDKPpHn8/z+Z6
Yw8JK5stX5VhRO6h+OfiV3WaHxtPVKAwmF+KqXUDMkgqZ0+UoKcEP57/GXOq
h46ODqrPXUQfufb6YOGxJOQD2CaHQnnlAJ4zDXggHBYvK6ap6Rau+RIz1k7d
jd+YHrqMpXUC4KQnWTRPAdiuRqNRkFQG/omRNJOsKVQw408xtS4QDsI10Aaq
EY6O8Fzq70fJy3XI8gsA5HTarBdOkvwFKj39EWrr/sJzEnj29OvsphGugfBs
LlwbZnjcYN36LxiLuADtMtCUetFAcE4ee8s+pbHVYtOemwhHx3MSaPEY3X9O
Unqsk5a2OMeP7KC3t4u+3gRALUC4cEW2eN62Q4ze3SAiz74TDxvOiI+XBcTs
oCoyfJKYn6OKmrMbxGRnlXhyJSSqv80Vq0KSAFa+ceKl0wcK9lfsW42TGsE/
pxhfcDmKfz6eFUPg4iRH6Ov6g9EJh1t341xusWuAyn9b+c7BrbklJ8oDZGTO
QpL9ePY08SmDpCEwbcHwuE370ykuNlj3gM/e90yXliyU9+8sCVJYlEUgU8Iw
BZruMThm83uzxsAYV4Hd/A9h4BjQBthAFOgDLgDF7w6/ArI6YJ0eTQeGAAAA
AElFTkSuQmCC

--=-KMpE3vvjAyPxVLJOdVFB--

--=-7ayU5FzAOyEqt87kUd90
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (FreeBSD)

iD8DBQBA4EeX21dVnhLsBV0RAmKnAJ9WqbGX++Q7mTP8Q/Zth+fLhLZ/hACdHEYO
5VKHzKsA6FgkXXZ3vtIbK/M=
=blhN
-----END PGP SIGNATURE-----

--=-7ayU5FzAOyEqt87kUd90--