Re: [GENERAL] Inheritance and foreign keys

[Jayadevan M]

On Thu, May 25, 2017 at 6:00 PM, Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> The way I do it is the following :
> - ensure a common sequence for the ID for all tables in the inheritance
> tree (usually one parent and one or more children)
> - enforce normal FK constraints for all FK relations within the same
> "realm"/"tenant"/"schema" etc, i.e. where it makes sense
> - for enforcing FK constraints between tables in different "realms", you
> should implement this as a pair of CONSTRAINT triggers which implement the
> two sides of the FK dependency. For the referencing tables you'd want to
> check upon INSERT or UPDATE, with smth like :
>
> CREATE OR REPLACE FUNCTION 
> public.accounting_docs_cases_fk_to_public_accounting_docs()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS $$
> DECLARE
> tmp INTEGER;
> BEGIN
> IF (TG_OP = 'DELETE') THEN
>   RAISE EXCEPTION 'TRIGGER : % called on unsuported op :
> %',TG_NAME, TG_OP;
> END IF;
> SELECT ad.id INTO tmp FROM public.accounting_docs ad WHERE ad.id
> =NEW.acct_doc_id;
> IF NOT FOUND THEN
>   RAISE EXCEPTION '%''d % (id=%) with NEW.acct_doc_id (%) does not
> match any accounting_docs ',TG_OP, TG_TABLE_NAME, NEW.id, NEW.acct_doc_id
> USING ERRCODE = 'foreign_key_violation';
> END IF;
> RETURN NEW;
> END
> $$
> ;
>
> -- here public.accounting_docs is a top level INHERITANCE table. Has
> bcompanyFOO.accounting_docs and bcompanyBAR.accounting_docs as inherited
> tables
>
> CREATE CONSTRAINT TRIGGER 
> accounting_docs_cases_fk_to_public_accounting_docs_tg
> AFTER INSERT OR UPDATE
> ON public.accounting_docs_cases FROM public.accounting_docs DEFERRABLE FOR
> EACH ROW EXECUTE PROCEDURE public.accounting_docs_cases_f
> k_to_public_accounting_docs();
>
> For the referenced tables you'd want to check upon UPDATE or DELETE with
> smth like :
>
> CREATE OR REPLACE FUNCTION 
> public.accounting_docs_fk_from_accounting_docs_cases()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS $$
> DECLARE
> tmp INTEGER;
> BEGIN
> IF (TG_OP = 'INSERT') THEN
>   RAISE EXCEPTION 'TRIGGER : % called on unsuported op :
> %',TG_NAME, TG_OP;
> END IF;
> IF (TG_OP = 'DELETE' OR OLD.id <> NEW.id) THEN
>   SELECT adc.id INTO tmp FROM accounting_docs_cases adc WHERE
> adc.acct_doc_id=OLD.id;
>   IF FOUND THEN
> RAISE EXCEPTION '%''d % (OLD id=%) matches existing
> accounting_docs_cases with id=%',TG_OP, TG_TABLE_NAME, OLD.id,tmp USING
> ERRCODE = 'foreign_key_violation';
>   END IF;
> END IF;
> IF (TG_OP = 'UPDATE') THEN
> RETURN NEW;
> ELSE
> RETURN OLD;
> END IF;
> END
> $$
> ;
>
> CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases
> AFTER DELETE OR UPDATE
> ON public.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR EACH
> ROW EXECUTE PROCEDURE accounting_docs_fk_from_accounting_docs_cases();
>
> CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases
> AFTER DELETE OR UPDATE
> ON bcompanyFOO.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR
> EACH ROW EXECUTE PROCEDURE accounting_docs_fk_from_accoun
> ting_docs_cases();
>
> CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases
> AFTER DELETE OR UPDATE
> ON bcompanyBAR.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR
> EACH ROW EXECUTE PROCEDURE accounting_docs_fk_from_accoun
> ting_docs_cases();
>
>
> Note that still this is not a proper case of a FK constraint, since this
> requires a true common unique index across all tables of the inheritance
> tree, which is not possible as of today.
>
>
Thank you. This should work for me.

Re: [GENERAL] Inheritance and foreign keys

[Achilleas Mantzios]

The way I do it is the following :
- ensure a common sequence for the ID for all tables in the inheritance tree 
(usually one parent and one or more children)
- enforce normal FK constraints for all FK relations within the same 
"realm"/"tenant"/"schema" etc, i.e. where it makes sense
- for enforcing FK constraints between tables in different "realms", you should implement this as a pair of CONSTRAINT triggers which implement the two sides of the FK dependency. For the referencing 
tables you'd want to check upon INSERT or UPDATE, with smth like :


CREATE OR REPLACE FUNCTION 
public.accounting_docs_cases_fk_to_public_accounting_docs() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
tmp INTEGER;
BEGIN
IF (TG_OP = 'DELETE') THEN
  RAISE EXCEPTION 'TRIGGER : % called on unsuported op : %',TG_NAME, 
TG_OP;
END IF;
SELECT ad.id INTO tmp FROM public.accounting_docs ad WHERE 
ad.id=NEW.acct_doc_id;
IF NOT FOUND THEN
  RAISE EXCEPTION '%''d % (id=%) with NEW.acct_doc_id (%) does not 
match any accounting_docs ',TG_OP, TG_TABLE_NAME, NEW.id, NEW.acct_doc_id USING 
ERRCODE = 'foreign_key_violation';
END IF;
RETURN NEW;
END
$$
;

-- here public.accounting_docs is a top level INHERITANCE table. Has 
bcompanyFOO.accounting_docs and bcompanyBAR.accounting_docs as inherited tables

CREATE CONSTRAINT TRIGGER accounting_docs_cases_fk_to_public_accounting_docs_tg 
AFTER INSERT OR UPDATE
ON public.accounting_docs_cases FROM public.accounting_docs DEFERRABLE FOR EACH 
ROW EXECUTE PROCEDURE 
public.accounting_docs_cases_fk_to_public_accounting_docs();

For the referenced tables you'd want to check upon UPDATE or DELETE with smth 
like :

CREATE OR REPLACE FUNCTION 
public.accounting_docs_fk_from_accounting_docs_cases() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
tmp INTEGER;
BEGIN
IF (TG_OP = 'INSERT') THEN
  RAISE EXCEPTION 'TRIGGER : % called on unsuported op : %',TG_NAME, 
TG_OP;
END IF;
IF (TG_OP = 'DELETE' OR OLD.id <> NEW.id) THEN
  SELECT adc.id INTO tmp FROM accounting_docs_cases adc WHERE 
adc.acct_doc_id=OLD.id;
  IF FOUND THEN
RAISE EXCEPTION '%''d % (OLD id=%) matches existing 
accounting_docs_cases with id=%',TG_OP, TG_TABLE_NAME, OLD.id,tmp USING ERRCODE 
= 'foreign_key_violation';
  END IF;
END IF;
IF (TG_OP = 'UPDATE') THEN
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END
$$
;

CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases AFTER 
DELETE OR UPDATE
ON public.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR EACH ROW 
EXECUTE PROCEDURE accounting_docs_fk_from_accounting_docs_cases();

CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases AFTER 
DELETE OR UPDATE
ON bcompanyFOO.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR EACH 
ROW EXECUTE PROCEDURE accounting_docs_fk_from_accounting_docs_cases();

CREATE CONSTRAINT TRIGGER accounting_docs_fk_from_accounting_docs_cases AFTER 
DELETE OR UPDATE
ON bcompanyBAR.accounting_docs FROM accounting_docs_cases DEFERRABLE FOR EACH 
ROW EXECUTE PROCEDURE accounting_docs_fk_from_accounting_docs_cases();


Note that still this is not a proper case of a FK constraint, since this 
requires a true common unique index across all tables of the inheritance tree, 
which is not possible as of today.

On 25/05/2017 14:48, Jayadevan M wrote:

Hi,

I designed three tables so that one table inherits another, and the third table references the parent table. If a record is inserted into the third table and the value does exist in the parent table 
indirectly, because it is present in the inherited table, I still get an error.

Is some option available while creating the foreign key so that it will 
consider the data in the child tables also while doing a constraint validation?

create table myt(id serial primary key);
create table mytc (like myt);
alter table mytc inherit myt;
insert into myt values(1);
insert into mytc values(2);
 select * from myt;
 id

  1
  2

create table a (id integerreferences myt(id));
insert into a values(2);
ERROR:  insert or update on table "a" violates foreign key constraint 
"a_id_fkey"
DETAIL:  Key (id)=(2) is not present in table "myt".


Regards,
Jayadevan



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and foreign keys

[Andreas Kretschmer]

Not possible - yet.

Am 25. Mai 2017 13:48:59 MESZ schrieb Jayadevan M :
>Hi,
>
>I designed three tables so that one table inherits another, and the
>third
>table references the parent table. If a record is inserted into the
>third
>table and the value does exist in the parent table indirectly, because
>it
>is present in the inherited table, I still get an error.
>Is some option available while creating the foreign key so that it will
>consider the data in the child tables also while doing a constraint
>validation?
>
>create table myt(id serial primary key);
>create table mytc (like myt);
>alter table mytc inherit myt;
>insert into myt values(1);
>insert into mytc values(2);
> select * from myt;
> id
>
>  1
>  2
>
>create table a (id integerreferences myt(id));
>insert into a values(2);
>ERROR:  insert or update on table "a" violates foreign key constraint
>"a_id_fkey"
>DETAIL:  Key (id)=(2) is not present in table "myt".
>
>
>Regards,
>Jayadevan

-- 
Diese Nachricht wurde von meinem Android-Mobiltelefon mit K-9 Mail gesendet.

[GENERAL] Inheritance and foreign keys

[Jayadevan M]

Hi,

I designed three tables so that one table inherits another, and the third
table references the parent table. If a record is inserted into the third
table and the value does exist in the parent table indirectly, because it
is present in the inherited table, I still get an error.
Is some option available while creating the foreign key so that it will
consider the data in the child tables also while doing a constraint
validation?

create table myt(id serial primary key);
create table mytc (like myt);
alter table mytc inherit myt;
insert into myt values(1);
insert into mytc values(2);
 select * from myt;
 id

  1
  2

create table a (id integerreferences myt(id));
insert into a values(2);
ERROR:  insert or update on table "a" violates foreign key constraint
"a_id_fkey"
DETAIL:  Key (id)=(2) is not present in table "myt".


Regards,
Jayadevan

Re: [GENERAL] Inheritance and trigger/FK propagation

[Davor J.]

Karsten Hilbert karsten.hilb...@gmx.net wrote in message 
news:20100728182051.gj2...@hermes.hilbert.loc...
 On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote:

 Well... I found it out the hard way :). There are some extra caveats I 
 have
 come along. There is the very clumsy ALTER TABLE table_name
 INHERIT(parent_table) which simply presupposes the parent's columns, but
 doesn't enforce it thereafter? So you can remove an inherited column from
 the child table when inheritance is made after the child table creation.

 Anyhow, I thought it could be quite usable for development a row level
 security system. For example, one could have a table  rls_security
 (rls_owner name, rls_select name, rls_delete name, rls_update name) and a
 simple trigger:

 While, as you found out, the trigger won't auto-propagate
 this approach is still useful !

 - let all tables inherit from a base table providing the rls fields

 - write a generic trigger that accesses the rls fields *only*
  (the table oid of the child table is available in the parent table
   row, fortunately, which will help making error messages better)

Interesting.


 - use an external script (or even plpgsql function) to
  attach said generic trigger to each table - the script
  does not need to know the list of relevant tables because
  that can be derived from the schema metadata inside PostgreSQL
  (they are children of the parent table ;-)

For completeness, I think this link 
(http://projects.nocternity.net/index.py/en/psql-inheritance) provides some 
scripts you mention. I haven't tested them, but I think they are great to 
start with.


 While a bit more cumbersome than (on-demand) trigger
 propagation it is still a fairly clean and
 close-to-the-ideal solution.

Now if Postgres supported firing triggers on CREATE TABLE (so these scripts 
could fire auto-magically), then it would have been even 
closer-to-the-ideal :)


 Karsten 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and trigger/FK propagation

[Karsten Hilbert]

On Thu, Jul 29, 2010 at 10:50:02AM +0200, Davor J. wrote:

 For completeness, I think this link 
 (http://projects.nocternity.net/index.py/en/psql-inheritance) provides some 
 scripts you mention.

Very interesting.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and trigger/FK propagation

[Karsten Hilbert]

On Tue, Jul 27, 2010 at 10:36:16AM +0200, Davor J. wrote:

 For me Vick's question just proves that inheritance in relational databases 
 is a complex issue. It shows that trigger propagation is not always desired, 

Now that's for sure :-)

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and trigger/FK propagation

[Karsten Hilbert]

On Tue, Jul 27, 2010 at 10:33:19AM +0200, Davor J. wrote:

 Well... I found it out the hard way :). There are some extra caveats I have 
 come along. There is the very clumsy ALTER TABLE table_name 
 INHERIT(parent_table) which simply presupposes the parent's columns, but 
 doesn't enforce it thereafter? So you can remove an inherited column from 
 the child table when inheritance is made after the child table creation.
 
 Anyhow, I thought it could be quite usable for development a row level 
 security system. For example, one could have a table  rls_security 
 (rls_owner name, rls_select name, rls_delete name, rls_update name) and a 
 simple trigger:

While, as you found out, the trigger won't auto-propagate
this approach is still useful !

- let all tables inherit from a base table providing the rls fields

- write a generic trigger that accesses the rls fields *only*
  (the table oid of the child table is available in the parent table
   row, fortunately, which will help making error messages better)

- use an external script (or even plpgsql function) to
  attach said generic trigger to each table - the script
  does not need to know the list of relevant tables because
  that can be derived from the schema metadata inside PostgreSQL
  (they are children of the parent table ;-)

While a bit more cumbersome than (on-demand) trigger
propagation it is still a fairly clean and
close-to-the-ideal solution.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and trigger/FK propagation

[Davor J.]

Karsten Hilbert karsten.hilb...@gmx.net wrote in message 
news:20100719182027.123...@gmx.net...
 On Thu, Jul 15, 2010 at 4:05 AM, Davor J. dav...@live.com wrote:
  It seems no secret that a child table will not fire a trigger defined 
  on
  it's parent table. Various posts comment on this. But nowhere could I
 find a
  reason for this.

 Do you want your trigger that redirects insert on parent table to the
 proper child table should run on child tables too?

 Well, inheritance is not used for partitioning ONLY. So, yes, for *my*
 use cases I would appreciate being able to tell triggers defined on
 parent tables to run on child tables when an insert/update/delete
 happens on a child table. (We use inheritance for auditing and for
 data aggregation.)

 But since I am not in a position to code the necessary infrastructure
 I won't complain about the status quo.

 Karsten


For me Vick's question just proves that inheritance in relational databases 
is a complex issue. It shows that trigger propagation is not always desired, 
contrary to what I believed.

But I also have to concur with Karsten.

Regards,
Davor 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and trigger/FK propagation

[Davor J.]

Craig Ringer cr...@postnewspapers.com.au wrote in message 
news:4c3ed37c.1070...@postnewspapers.com.au...
 My understanding is that it's mostly an implementation limitation. In
 other words, rather than any fundamental reason why it should not be
 done, the issue is that nobody has gone and implemented it, tested it,
 and ironed out the quirks and corner cases yet.

Well... I found it out the hard way :). There are some extra caveats I have 
come along. There is the very clumsy ALTER TABLE table_name 
INHERIT(parent_table) which simply presupposes the parent's columns, but 
doesn't enforce it thereafter? So you can remove an inherited column from 
the child table when inheritance is made after the child table creation.

Anyhow, I thought it could be quite usable for development a row level 
security system. For example, one could have a table  rls_security 
(rls_owner name, rls_select name, rls_delete name, rls_update name) and a 
simple trigger:

CREATE OR REPLACE FUNCTION rls_inherit_enforce()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN

CASE TG_OP
WHEN 'UPDATE' THEN
 IF NOT has_rowaccess(OLD.rls_update || OLD.rls_owner) THEN
  RAISE EXCEPTION 'No permission for update of row';
 END IF;
WHEN 'DELETE' THEN
 IF NOT has_rowaccess(OLD.rls_delete || OLD.rls_owner) THEN
  RAISE EXCEPTION 'No permission for deletion of row';
 END IF;
ELSE
 -- case when access type is not handled
 RAISE EXCEPTION 'Access type % not handled', TG_OP;
END CASE;

RETURN NEW;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE


Function has_rowaccess(name[]) would check whether the current/session_user 
is an admin or if he inherits any of the privileged passed-by users.

Now, with a proper (?) implementation of inheritance and trigger 
propagation, RLS could be enforced on any table which would inherit from 
rls_security.

In the end I dumped this approach and implemented something similar to Veil 
through plpgsql. Personally, I am not really a fan of statement-like (or 
constraint-based if you like) RLS, like GRANT user_name privilege_type TO 
query or something similar. For table/column privileges it is OK, but once 
you have to manage many users and many rows, such RLS systems tend to become 
unmanageable. But then again, this is MHO, and not really a place to discuss 
RLS. I just wanted to point out that inheritance might also be usable for 
some RLS implementation.

Regards,
Davor 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and trigger/FK propagation

[Vick Khera]

On Thu, Jul 15, 2010 at 4:05 AM, Davor J. dav...@live.com wrote:
 It seems no secret that a child table will not fire a trigger defined on
 it's parent table. Various posts comment on this. But nowhere could I find a
 reason for this.

Do you want your trigger that redirects insert on parent table to the
proper child table should run on child tables too?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and trigger/FK propagation

[Karsten Hilbert]

 On Thu, Jul 15, 2010 at 4:05 AM, Davor J. dav...@live.com wrote:
  It seems no secret that a child table will not fire a trigger defined on
  it's parent table. Various posts comment on this. But nowhere could I
 find a
  reason for this.
 
 Do you want your trigger that redirects insert on parent table to the
 proper child table should run on child tables too?

Well, inheritance is not used for partitioning ONLY. So, yes, for *my*
use cases I would appreciate being able to tell triggers defined on
parent tables to run on child tables when an insert/update/delete
happens on a child table. (We use inheritance for auditing and for
data aggregation.)

But since I am not in a position to code the necessary infrastructure
I won't complain about the status quo.

Karsten

wiki.gnumed.de

-- 
GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl.  
Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Inheritance and trigger/FK propagation

[Davor J.]

It seems no secret that a child table will not fire a trigger defined on 
it's parent table. Various posts comment on this. But nowhere could I find a 
reason for this.

Now, I just wonder whether the people who request this are wrong in their 
assumption that a trigger should fire on the child table, since those 
requests date from 2004 and are still not implemented?

As far as I see propagation has numerous advantages and not-propagation 
leads to maintenance problems resulting in data inconsistencies in case of 
designs where triggers should propagate. On the other hand, do any design(s) 
exist where there should be no propagation?

I think the same could be argued for FK propagation.

I read in the change logs of 8.4: Force child tables to inherit CHECK 
constraints from parents (Alex Hunsaker, Nikhil Sontakke, Tom). So why not 
with triggers and FK's?

Regards,
Davor 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance and trigger/FK propagation

[Craig Ringer]

On 15/07/10 16:05, Davor J. wrote:
 It seems no secret that a child table will not fire a trigger defined on 
 it's parent table. Various posts comment on this. But nowhere could I find a 
 reason for this.

[snip]

 I read in the change logs of 8.4: Force child tables to inherit CHECK 
 constraints from parents (Alex Hunsaker, Nikhil Sontakke, Tom). So why not 
 with triggers and FK's?

My understanding is that it's mostly an implementation limitation. In
other words, rather than any fundamental reason why it should not be
done, the issue is that nobody has gone and implemented it, tested it,
and ironed out the quirks and corner cases yet.

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Inheritance efficiency

[Vincenzo Romano]

Hi all.
I'd like to know how efficient is inheritance when the number of
children gets higher and higher.
I mean both with and without the constraint exclusion.

Will this change with 9.0 or possibly 9.1?

Thanks.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Vincenzo Romano]

2010/5/1 Greg Smith g...@2ndquadrant.com:
 Vincenzo Romano wrote:

 I argued that O(n) stuff will keep it away from enterprise grade
 applications.
 I've been told earlier that It is fine for dozens of child tables,
 but not thousands;
 it does need improvement.
 This is not enterprise grade

 Enterprise grade doesn't mean anything.  Partitioning designs that require
 thousands of child tables to work right are fundamentally misdesigned
 anyway, so there is no reason for any of the contributors to the project to
 work on improving support for them.  There are far too many obvious
 improvements that could be made to PostgreSQL, ones that will benefit vastly
 more people, to divert resources toward something you shouldn't be dong
 anyway like that.

While I can agree that Enterprise grade is a buzzword, it does mean
something: very large amount of data among other.
There's no fundamentally good design, but only a design which takes
limitations and constraints into account.

I just say that sublinear algorithms allow better handling for growing
numbers of objects.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
cel. +39 339 8083886  | gtalk. vincenzo.rom...@notorand.it
fix. +39 0823 454163  | skype. notorand.it
fax. +39 02 700506964 | msn.   notorand.it
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Greg Smith]

Vincenzo Romano wrote:

While I can agree that Enterprise grade is a buzzword, it does mean
something: very large amount of data among other.
  


http://thedailywtf.com/Articles/Bitten_by_the_Enterprise_Bug.aspx

It's quite straighforward to get PostgreSQL up and running with many 
terabytes of data, so long as you respect the design trade-offs in some 
options.  What you can't do is say those are wrong and reject 
alternative implementation suggestions just because they're not 
enterprise.  Whenever anyone uses that word at me, I mentally replace 
it with super duper, and



There's no fundamentally good design, but only a design which takes
limitations and constraints into account.
  


You mean like taking into account the fact that partitioning performance 
has an unavoidable trade-off, where you have to balance the query 
optimizer overhead of supporting many partitions against the improvement 
from splitting data into smaller pieces? 


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Vincenzo Romano]

2010/5/1 Greg Smith g...@2ndquadrant.com:
 Vincenzo Romano wrote:

 While I can agree that Enterprise grade is a buzzword, it does mean
 something: very large amount of data among other.


 http://thedailywtf.com/Articles/Bitten_by_the_Enterprise_Bug.aspx

 It's quite straighforward to get PostgreSQL up and running with many
 terabytes of data, so long as you respect the design trade-offs in some
 options.  What you can't do is say those are wrong and reject alternative
 implementation suggestions just because they're not enterprise.  Whenever
 anyone uses that word at me, I mentally replace it with super duper, and

 There's no fundamentally good design, but only a design which takes
 limitations and constraints into account.


 You mean like taking into account the fact that partitioning performance has
 an unavoidable trade-off, where you have to balance the query optimizer
 overhead of supporting many partitions against the improvement from
 splitting data into smaller pieces?

Or taking into account that some parts of the engine are not scalable.
Almost all current RDBMS can cope with terabytes.
Almost none (if any) can cope with large number of partial indexes (provided
they support them) or child tables or table level constraints and so on.
This is a fact as far as I've read so far.

Then we can discuss about the enterprise grade, the fault tolerance
and whatever else
buzzword pops up in our minds.
These ones maybe not.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[John R Pierce]

Greg Smith wrote:
Enterprise grade doesn't mean anything.  Partitioning designs that 
require thousands of child tables to work right are fundamentally 
misdesigned anyway, so there is no reason for any of the contributors 
to the project to work on improving support for them.  There are far 
too many obvious improvements that could be made to PostgreSQL, ones 
that will benefit vastly more people, to divert resources toward 
something you shouldn't be dong anyway like that.




my sql developer, who's been doing oracle for 15+ years, says postgres' 
partitioning is flawed from his perspective because if you have a 
prepared statement like..


   SELECT fields FROM partitioned_table WHERE primarykey = $1;

it doesn't optimize this very well and ends up looking at all the 
sub-table indicies.   ir you instead execute the statement


   SELECT fields FROM parritioned_table WHERE primarykey = constant;

he says the planner will go straight to the correct partition.

i haven't confirmed this for myself.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Cédric Villemain]

2010/5/1 John R Pierce pie...@hogranch.com:
 Greg Smith wrote:

 Enterprise grade doesn't mean anything.  Partitioning designs that require
 thousands of child tables to work right are fundamentally misdesigned
 anyway, so there is no reason for any of the contributors to the project to
 work on improving support for them.  There are far too many obvious
 improvements that could be made to PostgreSQL, ones that will benefit vastly
 more people, to divert resources toward something you shouldn't be dong
 anyway like that.


 my sql developer, who's been doing oracle for 15+ years, says postgres'
 partitioning is flawed from his perspective because if you have a prepared
 statement like..

   SELECT fields FROM partitioned_table WHERE primarykey = $1;

 it doesn't optimize this very well and ends up looking at all the sub-table
 indicies.   ir you instead execute the statement

   SELECT fields FROM parritioned_table WHERE primarykey = constant;

 he says the planner will go straight to the correct partition.

 i haven't confirmed this for myself.

It has nothing to do with partitionning but how the planner works.
Even if the use case remain correct




 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 
Cédric Villemain

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Alban Hertroys]

On 1 May 2010, at 5:33, John R Pierce wrote:

 Greg Smith wrote:
 my sql developer, who's been doing oracle for 15+ years, says postgres' 
 partitioning is flawed from his perspective because if you have a prepared 
 statement like..
 
   SELECT fields FROM partitioned_table WHERE primarykey = $1;
 
 it doesn't optimize this very well and ends up looking at all the sub-table 
 indicies.

Yes it would, for a very logical reason.

A prepared statement is nothing but a stored query plan - its benefits are 
mostly that you can skip the query planning step before performing a query, 
which helps queries that are performed very frequently in a short time or that 
take a long time planning.

But skipping the query planner also has a drawback; the planner has to make a 
general assumption about what kind of data you'll be querying. It can't vary 
the query plan depending on what data you're querying for.

If someone is writing a query on a partitioned table and wants to rely on 
constraint exclusion and they're trying to use a prepared statement then they 
don't understand what prepared statements are.

You could argue that some logic could be added to the handling of prepared 
statements to insert query-subplans depending on what data you use for your 
parameters, but then you're moving back in the direction of unprepared 
statements (namely invoking the query planner). It would help cases like this 
one, but it would hurt all other prepared statements. It would at the least add 
a parse tree back into the queries path, which would be a fairly simplistic one 
in the case of table partitioning, but would get fairly complex for prepared 
statements involving more parameters - so much so that the benefit of using a 
prepared statement (not spending time planning the query) would get reduced 
significantly.
It's possible that Oracle implemented something like this, but as you see it's 
not necessarily an improvement.

In practice people either query the correct table partition directly or do not 
use a prepared statement.

   ir you instead execute the statement
 
   SELECT fields FROM parritioned_table WHERE primarykey = constant;
 
 he says the planner will go straight to the correct partition.
 
 i haven't confirmed this for myself.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bdc08fc10416246414315!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Alban Hertroys]

On 1 May 2010, at 12:56, Alban Hertroys wrote:

 You could argue that some logic could be added to the handling of prepared 
 statements to insert query-subplans depending on what data you use for your 
 parameters, but then you're moving back in the direction of unprepared 
 statements (namely invoking the query planner). It would help cases like this 
 one, but it would hurt all other prepared statements. It would at the least 
 add a parse tree back into the queries path, which would be a fairly 
 simplistic one in the case of table partitioning, but would get fairly 
 complex for prepared statements involving more parameters - so much so that 
 the benefit of using a prepared statement (not spending time planning the 
 query) would get reduced significantly.


And of course it would add time for planning the query-tree to the creation of 
the prepared statement - which could be significant compared to the time people 
expect to save by not invoking the planner on later invocations of the same 
query. That said, the more frequent the query is executed the less that hurts 
performance, while it doesn't really matter for queries that are executed 
infrequently.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4bdc0ba010411331128920!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Vincenzo Romano]

2010/4/30 David Fetter da...@fetter.org:
 On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote:
  No info about this point (partial indexes)?
  Is also this geared with linear algorithms ?

 Should I move to an enterprise grade version of PostgreSQL?

 The enterprise grade version of PostgreSQL is the community version.

 Proprietary forks exist, but they don't fix this kind of problem. :)

Hmmm ... I think this is the kind of problems that keeps PostgreSQL away
from the enterprise grade world.
The ability to cope with thousands of DB objects like (child-)tables,
indexes, functions and so on with
O(1) or at least O(log(n))  complexity is among the key points.

For example, the Linux kernel made the big jump with server hardware
thanks also to the O(1) schedulers.

In this specific case, if you think about inheritance for
partitioning and you stick with the example idea of one partition
per month, then the current solution is more than OK.
In the real world, that is not really the general case, especially in
the enterprise grade world, where maybe you partition with both a
time stamp and another column, like product code ranges and prefixes
...

Is there any planning about this improvement?




 Cheers,
 David.
 --
 David Fetter da...@fetter.org http://fetter.org/
 Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
 Skype: davidfetter      XMPP: david.fet...@gmail.com
 iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

 Remember to vote!
 Consider donating to Postgres: http://www.postgresql.org/about/donate




-- 
Vincenzo Romano
NotOrAnd Information Technologies
cel. +39 339 8083886  | gtalk. vincenzo.rom...@notorand.it
fix. +39 0823 454163  | skype. notorand.it
fax. +39 02 700506964 | msn.   notorand.it
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Vincenzo Romano]

2010/4/30 Vincenzo Romano vincenzo.rom...@notorand.it:
 2010/4/30 David Fetter da...@fetter.org:
 On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote:
  No info about this point (partial indexes)?
  Is also this geared with linear algorithms ?

 Should I move to an enterprise grade version of PostgreSQL?

 The enterprise grade version of PostgreSQL is the community version.

 Proprietary forks exist, but they don't fix this kind of problem. :)

 Hmmm ... I think this is the kind of problems that keeps PostgreSQL away
 from the enterprise grade world.
 The ability to cope with thousands of DB objects like (child-)tables,
 indexes, functions and so on with
 O(1) or at least O(log(n))  complexity is among the key points.

 For example, the Linux kernel made the big jump with server hardware
 thanks also to the O(1) schedulers.

 In this specific case, if you think about inheritance for
 partitioning and you stick with the example idea of one partition
 per month, then the current solution is more than OK.
 In the real world, that is not really the general case, especially in
 the enterprise grade world, where maybe you partition with both a
 time stamp and another column, like product code ranges and prefixes
 ...

 Is there any planning about this improvement?

Could it be possible to just make some changes (adding indexes) to the
information schema
to gain this enterprise gradeness?

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[David Fetter]

On Fri, Apr 30, 2010 at 08:44:26AM +0200, Vincenzo Romano wrote:
  Should I move to an enterprise grade version of PostgreSQL?
 
  The enterprise grade version of PostgreSQL is the community
  version.
 
  Proprietary forks exist, but they don't fix this kind of problem.
  :)
 
  Hmmm ... I think this is the kind of problems that keeps
  PostgreSQL away from the enterprise grade world.  The ability to
  cope with thousands of DB objects like (child-)tables, indexes,
  functions and so on with O(1) or at least O(log(n))  complexity is
  among the key points.
 
  For example, the Linux kernel made the big jump with server
  hardware thanks also to the O(1) schedulers.
 
  In this specific case, if you think about inheritance for
  partitioning and you stick with the example idea of one
  partition per month, then the current solution is more than OK.
  In the real world, that is not really the general case, especially
  in the enterprise grade world, where maybe you partition with
  both a time stamp and another column, like product code ranges and
  prefixes ...
 
  Is there any planning about this improvement?
 
 Could it be possible to just make some changes (adding indexes) to
 the information schema to gain this enterprise gradeness?

Your assertion that PostgreSQL is not enterprise grade is simply
false.  For years, it has been and continues to be used as the basis
of extremely large mission-critical systems.

That said, if you wish to make changes, or propose that some be made,
please feel free to do so after 9.0 comes out.

In the mean time, please test 9.0beta1 along with any ensuing betas
and release candidates, and report back the results of the
aforementioned testing.

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Alvaro Herrera]

Vincenzo Romano wrote:

 In this specific case, if you think about inheritance for
 partitioning and you stick with the example idea of one partition
 per month, then the current solution is more than OK.
 In the real world, that is not really the general case, especially in
 the enterprise grade world, where maybe you partition with both a
 time stamp and another column, like product code ranges and prefixes
 ...
 
 Is there any planning about this improvement?

Of course.  People is always looking to make improvements in many areas.
There are very few things that people consider to be more than OK.
The partitioning features are among those being more examined for
possibly improvements.

This does *not* mean that PostgreSQL doesn't serve mission critical
systems already, on enterprises large and small, some of them on very
large systems.  What you see in these lists (people describing
partition by month schemes) are not necessarily the most complex
setups out there.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Vincenzo Romano]

2010/4/30 Alvaro Herrera alvhe...@commandprompt.com:
 Vincenzo Romano wrote:

 In this specific case, if you think about inheritance for
 partitioning and you stick with the example idea of one partition
 per month, then the current solution is more than OK.
 In the real world, that is not really the general case, especially in
 the enterprise grade world, where maybe you partition with both a
 time stamp and another column, like product code ranges and prefixes
 ...

 Is there any planning about this improvement?

 Of course.  People is always looking to make improvements in many areas.
 There are very few things that people consider to be more than OK.
 The partitioning features are among those being more examined for
 possibly improvements.

 This does *not* mean that PostgreSQL doesn't serve mission critical
 systems already, on enterprises large and small, some of them on very
 large systems.  What you see in these lists (people describing
 partition by month schemes) are not necessarily the most complex
 setups out there.

Hi.
I've nerver meant to say that PG is not mission critical!
I argued that O(n) stuff will keep it away from enterprise grade applications.
I've been told earlier that It is fine for dozens of child tables,
but not thousands;
it does need improvement.

This is not enterprise grade.
And the same could go for (a large number of) partial indexes.
Any idea here?

Infact I have in mind also a different approach to partitioning which
could be useful (under certain constraints, of course).
Instead of partitioning the table itself, you can partition the indexes.
The data can still be in a single table (for the sake of some FKs for example).
Just the indexes get partitioned·
But, of course, a lot depends on whether the selection of the right indexes
(among thousands) is effective or not.


 --
 Alvaro Herrera                                http://www.CommandPrompt.com/
 PostgreSQL Replication, Consulting, Custom Development, 24x7 support




-- 
Vincenzo Romano
NotOrAnd Information Technologies
cel. +39 339 8083886  | gtalk. vincenzo.rom...@notorand.it
fix. +39 0823 454163  | skype. notorand.it
fax. +39 02 700506964 | msn.   notorand.it
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Alvaro Herrera]

Vincenzo Romano wrote:

 This is not enterprise grade.

Enterprise grade is nothing but a buzzword.  Oh, it's also a moving
target.  We've been not enterprise grade for years, always one feature
behind (and strangely, the one lacking feature is always the one of
interest to the complainant).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Bruce Momjian]

Alvaro Herrera wrote:
 Vincenzo Romano wrote:
 
  This is not enterprise grade.
 
 Enterprise grade is nothing but a buzzword.  Oh, it's also a moving
 target.  We've been not enterprise grade for years, always one feature
 behind (and strangely, the one lacking feature is always the one of
 interest to the complainant).

We do have this enhancement coming in Postgres 9.0:

 Add an index on pg_inherits.inhparent, and use it to avoid seqscans
 in find_inheritance_children().  This is a complete no-op in databases
 without any inheritance.  In databases where there are just a few
 entries in pg_inherits, it could conceivably be a small loss.  However,
 in databases with many inheritance parents, it can be a big win.

However, I don't think this going to help a lot for partitioning because
the cost is mostly checking the CHECK constraints, not finding the
table's children.

Like all Postgres missing features, we just need someone with time to
volunteer to research and fix it.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Alex Hunsaker]

On Fri, Apr 30, 2010 at 00:19, Vincenzo Romano
vincenzo.rom...@notorand.it wrote:
 For example, the Linux kernel made the big jump with server hardware
 thanks also to the O(1) schedulers.

flamebait
Uhh linux has not had a O(1) scheduler since 2.6.23, its supposedly
O(log n) now. =)
/flamebait

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Greg Smith]

Vincenzo Romano wrote:

I argued that O(n) stuff will keep it away from enterprise grade applications.
I've been told earlier that It is fine for dozens of child tables,
but not thousands;
it does need improvement.
This is not enterprise grade


Enterprise grade doesn't mean anything.  Partitioning designs that 
require thousands of child tables to work right are fundamentally 
misdesigned anyway, so there is no reason for any of the contributors to 
the project to work on improving support for them.  There are far too 
many obvious improvements that could be made to PostgreSQL, ones that 
will benefit vastly more people, to divert resources toward something 
you shouldn't be dong anyway like that.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Vincenzo Romano]

2010/4/26 Vincenzo Romano vincenzo.rom...@notorand.it:
 2010/4/26 Bruce Momjian br...@momjian.us:
 Vincenzo Romano wrote:
 Hi all.

 I'm wondering how efficient the inheritance can be.
 I'm using the constraint exclusion feature and for each child table
 (maybe but one) I have a proper CHECK constraint.
 How efficient can the query planner be in choosing the right child
 tables in the case of, say, thousands of them?
 Would the selection process behave linearly, logarithmically or what?

 It is fine for dozens of child tables, but not thousands; it does need
 improvement.

 This sounds like linear algorithms. Doesn't it?

 And now it comes to my mind the same question for partial indexes.
 That is, if I had a lot (really a lot) of small partial indexes over a
 very large table, how efficient can the query planner be
 in selecting the right indexes?

 No info about this point (partial indexes)?
 Is also this geared with linear algorithms ?

Should I move to an enterprise grade version of PostgreSQL?

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[David Fetter]

On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote:
  No info about this point (partial indexes)?
  Is also this geared with linear algorithms ?
 
 Should I move to an enterprise grade version of PostgreSQL?

The enterprise grade version of PostgreSQL is the community version.

Proprietary forks exist, but they don't fix this kind of problem. :)

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Inheritance efficiency

[Vincenzo Romano]

Hi all.

I'm wondering how efficient the inheritance can be.
I'm using the constraint exclusion feature and for each child table
(maybe but one) I have a proper CHECK constraint.
How efficient can the query planner be in choosing the right child
tables in the case of, say, thousands of them?
Would the selection process behave linearly, logarithmically or what?


And now it comes to my mind the same question for partial indexes.
That is, if I had a lot (really a lot) of small partial indexes over a
very large table, how efficient can the query planner be
in selecting the right indexes?

Will these answers change in v9?

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Bruce Momjian]

Vincenzo Romano wrote:
 Hi all.
 
 I'm wondering how efficient the inheritance can be.
 I'm using the constraint exclusion feature and for each child table
 (maybe but one) I have a proper CHECK constraint.
 How efficient can the query planner be in choosing the right child
 tables in the case of, say, thousands of them?
 Would the selection process behave linearly, logarithmically or what?

It is fine for dozens of child tables, but not thousands; it does need
improvement.


 And now it comes to my mind the same question for partial indexes.
 That is, if I had a lot (really a lot) of small partial indexes over a
 very large table, how efficient can the query planner be
 in selecting the right indexes?
 
 Will these answers change in v9?

No.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance efficiency

[Vincenzo Romano]

2010/4/26 Bruce Momjian br...@momjian.us:
 Vincenzo Romano wrote:
 Hi all.

 I'm wondering how efficient the inheritance can be.
 I'm using the constraint exclusion feature and for each child table
 (maybe but one) I have a proper CHECK constraint.
 How efficient can the query planner be in choosing the right child
 tables in the case of, say, thousands of them?
 Would the selection process behave linearly, logarithmically or what?

 It is fine for dozens of child tables, but not thousands; it does need
 improvement.

This sounds like linear algorithms. Doesn't it?

 And now it comes to my mind the same question for partial indexes.
 That is, if I had a lot (really a lot) of small partial indexes over a
 very large table, how efficient can the query planner be
 in selecting the right indexes?

No info about this point (partial indexes)?
Is also this geared with linear algorithms ?

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance on foreign key

[decibel]

On Oct 12, 2009, at 1:21 PM, Erik Jones wrote:

On Oct 9, 2009, at 9:57 AM, Takeichi Kanzaki Cabrera wrote:

Hi everybody, I need your help. I have a hierarchy of tables, and
other table that has a foreign key with the top table of the
hierarchy, can I insert a value into the other table where the  
value

it reference is not on the parent table? (it's in one of its child)


No, foreign key checks do not (yet) follow inheritance  
hierarchies.  Here's the specific clause in the manual (http:// 
www.postgresql.org/docs/8.4/interactive/ddl-inherit.html) that  
covers this:


All check constraints and not-null constraints on a parent table  
are automatically inherited by its children. Other types of  
constraints (unique, primary key, and foreign key constraints) are  
not inherited.



That said, there are ways around this. We're using inheritance to  
deal with things like customer accounts such as bank accounts,  
debit cards, etc. There's stuff that all of these have in common, and  
stuff that's specific, so the bank_account and debit_card tables each  
inherit from a customer_account table.  
customer_account.customer_account_type_id specifies what type of  
account a record is. Using that, we have a trigger that you can put  
on some other table that's referencing  
customer_account.customer_account_id; that trigger implements part of  
the functionality of a true foreign key. It only handles certain  
cases because that's all we need, but I believe you should be able to  
provide full foreign key support if you wanted to create all the  
right trigger functions. The key is to have the trigger function look  
at the parent table to determine what type of account / record it is,  
and then use that information to go to the appropriate child table  
and acquire a FOR UPDATE lock.


I can probably provide a more concrete example of this if anyone's  
interested.

--
Decibel!, aka Jim C. Nasby, Database Architect  deci...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance on foreign key

[decibel]

On Oct 12, 2009, at 1:21 PM, Erik Jones wrote:

On Oct 9, 2009, at 9:57 AM, Takeichi Kanzaki Cabrera wrote:

Hi everybody, I need your help. I have a hierarchy of tables, and
other table that has a foreign key with the top table of the
hierarchy, can I insert a value into the other table where the  
value

it reference is not on the parent table? (it's in one of its child)


No, foreign key checks do not (yet) follow inheritance  
hierarchies.  Here's the specific clause in the manual (http:// 
www.postgresql.org/docs/8.4/interactive/ddl-inherit.html) that  
covers this:


All check constraints and not-null constraints on a parent table  
are automatically inherited by its children. Other types of  
constraints (unique, primary key, and foreign key constraints) are  
not inherited.



That said, there are ways around this. We're using inheritance to  
deal with things like customer accounts such as bank accounts,  
debit cards, etc. There's stuff that all of these have in common, and  
stuff that's specific, so the bank_account and debit_card tables each  
inherit from a customer_account table.  
customer_account.customer_account_type_id specifies what type of  
account a record is. Using that, we have a trigger that you can put  
on some other table that's referencing  
customer_account.customer_account_id; that trigger implements part of  
the functionality of a true foreign key. It only handles certain  
cases because that's all we need, but I believe you should be able to  
provide full foreign key support if you wanted to create all the  
right trigger functions. The key is to have the trigger function look  
at the parent table to determine what type of account / record it is,  
and then use that information to go to the appropriate child table  
and aquire a FOR UPDATE lock.

--
Decibel!, aka Jim C. Nasby, Database Architect  deci...@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance on foreign key

[Erik Jones]

On Oct 9, 2009, at 9:57 AM, Takeichi Kanzaki Cabrera wrote:


Hi everybody, I need your help. I have a hierarchy of tables, and
other table that has a foreign key with the top table of the
hierarchy, can I insert a value into the other table where the value
it reference is not on the parent table? (it's in one of its child)


No, foreign key checks do not (yet) follow inheritance hierarchies.   
Here's the specific clause in the manual (http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html 
) that covers this:


All check constraints and not-null constraints on a parent table are  
automatically inherited by its children. Other types of constraints  
(unique, primary key, and foreign key constraints) are not inherited.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Inheritance on foreign key

[Takeichi Kanzaki Cabrera]

Hi everybody, I need your help. I have a hierarchy of tables, and
other table that has a foreign key with the top table of the
hierarchy, can I insert a value into the other table where the value
it reference is not on the parent table? (it's in one of its child)

My best regards.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Inheritance question

[Glyn Astill]

Hi chaps,

I've got a question about inheritance here, and I think I may have gotten the 
wrong end of the stick as to how it works, or at least when to use it.

What I intended to do was have a schema audit with an empty set of tables in 
it, then each quarter restore our audit data into schemas such as 
audit_Q1_2009 etc. Then alter the tables in the audit_Q1_2009 schema to 
inherit the audit schema, etc and so on for audit_Q2_2009.

This appears to work so the audit schema appears as if it contains everything 
in the other schemas.

However this isn't very efficient as soon as I try to order the data, even with 
only one table getting inherited it does a sort rather than using the index on 
the child table.

Is this because the inheritance works like a view, and it basically has to 
build the view before ordering it?

For example in audit_Q1_2009 the table at_price has an index on trigger_id

SEE=# explain select * from audit.at_price order by trigger_id limit 100;
 QUERY PLAN

 Limit  (cost=100095726.71..100095726.96 rows=100 width=820)
   -  Sort  (cost=100095726.71..100098424.83 rows=1079251 width=820)
 Sort Key: audit.at_price.trigger_id
 -  Result  (cost=0.00..54478.51 rows=1079251 width=820)
   -  Append  (cost=0.00..54478.51 rows=1079251 width=820)
 -  Seq Scan on at_price  (cost=0.00..10.90 rows=90 
width=820)
 -  Seq Scan on at_price  (cost=0.00..54467.61 
rows=1079161 width=280)


SEE=# explain select * from audit_Q1_2009.at_price order by trigger_id limit 
100;
   QUERY PLAN

 Limit  (cost=0.00..7.37 rows=100 width=280)
   -  Index Scan using at_price_pkey on at_price  (cost=0.00..79537.33 
rows=1079161 width=280)
(2 rows)


Any suggestions would be appreciated.






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance question

[Erik Jones]

On Jan 16, 2009, at 9:49 AM, Glyn Astill wrote:


Hi chaps,

I've got a question about inheritance here, and I think I may have  
gotten the wrong end of the stick as to how it works, or at least  
when to use it.


What I intended to do was have a schema audit with an empty set of  
tables in it, then each quarter restore our audit data into schemas  
such as audit_Q1_2009 etc. Then alter the tables in the  
audit_Q1_2009 schema to inherit the audit schema, etc and so on for  
audit_Q2_2009.


This appears to work so the audit schema appears as if it contains  
everything in the other schemas.


However this isn't very efficient as soon as I try to order the  
data, even with only one table getting inherited it does a sort  
rather than using the index on the child table.


Is this because the inheritance works like a view, and it basically  
has to build the view before ordering it?


Pretty much.  Inheritance works essentially like UNION ALL and for  
UNION queries the sort doesn't happen until the entire result set has  
been processed and even with processing on child table you're really  
processing 2 tables, the child and parent.  Think about it like this,  
for any given index that you may order by on the child tables there's  
no guarantee that you could do the same with all of the child tables,  
append the results and still have a correctly ordered result set.   
*We* may know that the tables have a logical ordering such that when  
processed in a certain order, sorting each along the way, the results  
from each child could be appended and maintain ordering of the results  
but the planner has no idea of anything like that.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance. more.

[Jeremy Harris]

Gurjeet Singh wrote:

 One of the advantages
of breaking up your data into partitions, as professed by Simon (I think)
(and I agree), is that you have smaller indexes, which improve performance.
And maybe having one huge index managing the uniqueness across partitioned
data just defeats the idea of data partitioning!


Isn't large indexes are a performance problem just saying
we don't implement indexes very well?   And why are they
a problem - surely a tree-structured index is giving you
range-partitioned subsets as you traverse it?  Why is this
different from manual partitioning into (inherited) tables?

Thanks,
Jeremy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance. more.

[Nathan Boley]

Because people can be smarter about the data partitioning.

Consider a table of users. Some are active, most are not. The active
users account for nearly all of the users table access, but I still
(occasionally) want to access info about the inactive users.
Partitioning users into active_users and inactive_users allows me to
tell the database (indirectly) that the active users index should stay
in memory, while the inactive users can relegated to disk.

-Nathan

On Thu, May 1, 2008 at 6:02 AM, Jeremy Harris [EMAIL PROTECTED] wrote:
 Gurjeet Singh wrote:

   One of the advantages
  of breaking up your data into partitions, as professed by Simon (I think)
  (and I agree), is that you have smaller indexes, which improve
 performance.
  And maybe having one huge index managing the uniqueness across partitioned
  data just defeats the idea of data partitioning!
 

  Isn't large indexes are a performance problem just saying
  we don't implement indexes very well?   And why are they
  a problem - surely a tree-structured index is giving you
  range-partitioned subsets as you traverse it?  Why is this
  different from manual partitioning into (inherited) tables?

  Thanks,
 Jeremy



  --
  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance. more.

[Jeremy Harris]

Nathan Boley wrote:

Because people can be smarter about the data partitioning.

Consider a table of users. Some are active, most are not. The active
users account for nearly all of the users table access, but I still
(occasionally) want to access info about the inactive users.
Partitioning users into active_users and inactive_users allows me to
tell the database (indirectly) that the active users index should stay
in memory, while the inactive users can relegated to disk.

-Nathan

On Thu, May 1, 2008 at 6:02 AM, Jeremy Harris [EMAIL PROTECTED] wrote:

Gurjeet Singh wrote:


 One of the advantages
of breaking up your data into partitions, as professed by Simon (I think)
(and I agree), is that you have smaller indexes, which improve

performance.

And maybe having one huge index managing the uniqueness across partitioned
data just defeats the idea of data partitioning!


 Isn't large indexes are a performance problem just saying
 we don't implement indexes very well?   And why are they
 a problem - surely a tree-structured index is giving you
 range-partitioned subsets as you traverse it?  Why is this
 different from manual partitioning into (inherited) tables?


Agreed, data placement is one reason for partitioning.  But won't
this happen automatically?  Won't, in your example, the active
part of a one-large-index stay in memory while the inactive parts
get pushed out?

Cheers,
  Jeremy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance. more.

[Gregory Stark]

Nathan Boley [EMAIL PROTECTED] writes:

 Because people can be smarter about the data partitioning.

 Consider a table of users. Some are active, most are not. The active
 users account for nearly all of the users table access, but I still
 (occasionally) want to access info about the inactive users.
 Partitioning users into active_users and inactive_users allows me to
 tell the database (indirectly) that the active users index should stay
 in memory, while the inactive users can relegated to disk.

(Someone's going to mumble something about partial indexes here.)

The 50,000 ft view of partitioning is it:

a) Lets the database do some work in query plan time instead of at run-time.
   So yes, an index would let you skip scanning parts of the table but you
   still have to do a few comparisons and page accesses on your index at
   run-time. On a partitioned table you do that same work (and it's harder)
   but at plan time.

b) Lets you partition based on a key which isn't indexed at all. Consider in
   the above scenario if you then run a query across *all* active users. Even
   partial indexes won't be very fast but a partitioned table can do a
   sequential scan of a single partition.

c) Makes loading pre-organized segments of data and dropping segments O(1)
   which is makes the data much more manageable.

It's really (c) which is the killer app for partitioned tables. (a) and (b)
are usually just nice side-shows.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance. more.

[Gurjeet Singh]

On Tue, Apr 29, 2008 at 11:47 AM, Martijn van Oosterhout [EMAIL PROTECTED]
wrote:

 On Tue, Apr 29, 2008 at 06:31:30AM +0530, Gurjeet Singh wrote:
  On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones [EMAIL PROTECTED] wrote:
   Postgres doesn't yet handle inheritance of constraints from parent to
   child tables via inheritance.

  Was it done by design or was it a limitation we couldn't get over?

 Inheritence of most constraints works, just not unique constraints. The
 problem of managing a unique index over multiple tables has not yet
 been solved (it's a reasonably hard problem).


I completely agree with the difficulty of the problem. One of the advantages
of breaking up your data into partitions, as professed by Simon (I think)
(and I agree), is that you have smaller indexes, which improve performance.
And maybe having one huge index managing the uniqueness across partitioned
data just defeats the idea of data partitioning!

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] inheritance. more.

[Karsten Hilbert]

 And maybe having one huge index managing the uniqueness across partitioned
 data just defeats the idea of data partitioning!
Except when you want uniqueness across all partitions.

Karsten

-- 
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! 
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance. more.

[Tom Lane]

Karsten Hilbert [EMAIL PROTECTED] writes:
 And maybe having one huge index managing the uniqueness across partitioned
 data just defeats the idea of data partitioning!

 Except when you want uniqueness across all partitions.

Well, the point was that if the partitioning arrangement guarantees to
put distinct ranges of the key into distinct tables, then a separate
unique constraint on each table would suffice to guarantee global
uniqueness.

You can set up such a thing today, but it's a manual jury-rigged affair.
An automatic partitioning system would be a lot nicer.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance. more.

[Martijn van Oosterhout]

On Tue, Apr 29, 2008 at 06:31:30AM +0530, Gurjeet Singh wrote:
 On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones [EMAIL PROTECTED] wrote:
  Postgres doesn't yet handle inheritance of constraints from parent to
  child tables via inheritance.

 Was it done by design or was it a limitation we couldn't get over?

Inheritence of most constraints works, just not unique constraints. The
problem of managing a unique index over multiple tables has not yet
been solved (it's a reasonably hard problem).

Have a nice day,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 Please line up in a tree and maintain the heap invariant while 
 boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [GENERAL] inheritance. more.

[Erik Jones]

On Apr 27, 2008, at 8:23 PM, Tom Allison wrote:


create table master (
id serial,
mdn varchar(11),
meid varchar(18),
min varchar(11),
constraint mmm_master unique (mdn, meid, min)
);
insert into master(mdn, meid, min)
select mdn, meid, min from test_data where meid !=  
'00' limit 10;


Everything works up to this point...

insert into master(mdn, meid, min)
select mdn, meid, min from test_data where meid !=  
'00' limit 10;


And this fails, like I would expect it to.


create table slave (
deleted boolean default false
) inherits (master);

insert into slave(mdn, meid, min)
select mdn, meid, min from test_data where meid !=  
'00' limit 10;

insert into slave(mdn, meid, min)
select mdn, meid, min from test_data where meid !=  
'00' limit 10;


I now have 30 rows in the master table, with duplicates...


No, you don't.  You have duplicates in slave, not master, and there is  
not unique constraint on slave.  They are physically separate tables  
and Postgres doesn't yet handle inheritance of constraints from parent  
to child tables via inheritance.


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate  market in style.
Visit us online at http://www.myemma.com




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance. more.

[Gurjeet Singh]

On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones [EMAIL PROTECTED] wrote:


 Postgres doesn't yet handle inheritance of constraints from parent to
 child tables via inheritance.


Was it done by design or was it a limitation we couldn't get over?

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] inheritance. more.

[Erik Jones]

On Apr 28, 2008, at 8:01 PM, Gurjeet Singh wrote:


On Mon, Apr 28, 2008 at 8:01 PM, Erik Jones [EMAIL PROTECTED] wrote:

Postgres doesn't yet handle inheritance of constraints from parent  
to child tables via inheritance.



Was it done by design or was it a limitation we couldn't get over?


My understanding of the lack of a full featured partitioning solution  
(based on previous conversations with Tom Lane, Gregory Stark, and the  
like) is that the current implementation was pieced together from  
other portions of the system -- i.e. the moving parts on the backend  
weren't built from the ground up with partitioning in mind.  I'm  
currently working on a command line tool that will take a table name  
along with a date/timestamp or integer based column on that table and  
some optional parameters and write out range based partitions for that  
table.  If you'd like I'll save this email and once I've got it stable  
and well tested I'll put it up in a public repo somewhere.


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate  market in style.
Visit us online at http://www.myemma.com




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] inheritance...

[Tom Allison]

Ran into something really unexpected, but then I've never tried using 
inherited tables.


I have a master table (named master) that has two child tables.

create table master (
  id serial,
  foo varchar(20),
  bar varchar(20),
  constraint foobar_master unique (foo,bar)
);

Now when I do this with just a table, the unique constraint works.

But when I have child tables:

create table slave (
  status varchar(20),
  deleted boolean default false
) inherits (master);

I seem to lose that unique constraint.  Which makes for a HUGE problem.

Am I missing something in the fine print?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] inheritance. more.

[Tom Allison]

create table master (
id serial,
mdn varchar(11),
meid varchar(18),
min varchar(11),
constraint mmm_master unique (mdn, meid, min)
);
insert into master(mdn, meid, min)
select mdn, meid, min from test_data where meid != '00' 
limit 10;


Everything works up to this point...

insert into master(mdn, meid, min)
select mdn, meid, min from test_data where meid != '00' 
limit 10;


And this fails, like I would expect it to.


create table slave (
deleted boolean default false
) inherits (master);

insert into slave(mdn, meid, min)
select mdn, meid, min from test_data where meid != '00' 
limit 10;

insert into slave(mdn, meid, min)
select mdn, meid, min from test_data where meid != '00' 
limit 10;


I now have 30 rows in the master table, with duplicates...

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] inheritance...

[Klint Gore]

Tom Allison wrote:

Am I missing something in the fine print?
fine print = see 5.8.1 Caveats on 
http://www.postgresql.org/docs/8.3/interactive/ddl-inherit.html


klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Inheritance problem when restoring db

[Sebastjan Trepca]

Great, super thanks!

Sebastjan

On 10/29/07, Tom Lane [EMAIL PROTECTED] wrote:
 I wrote:
  Sebastjan Trepca [EMAIL PROTECTED] writes:
  This is how to reproduce this issue:
  ...
  inh_test=# alter table capitals inherit cities;

  Fascinating.  pg_dump is almost smart enough to get this right, ...

 I've fixed this --- if you need a patch right away, see here:
 http://archives.postgresql.org/pgsql-committers/2007-10/msg00448.php

 regards, tom lane


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Inheritance problem when restoring db

[Tom Lane]

I wrote:
 Sebastjan Trepca [EMAIL PROTECTED] writes:
 This is how to reproduce this issue:
 ...
 inh_test=# alter table capitals inherit cities;

 Fascinating.  pg_dump is almost smart enough to get this right, ...

I've fixed this --- if you need a patch right away, see here:
http://archives.postgresql.org/pgsql-committers/2007-10/msg00448.php

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Inheritance problem when restoring db

[Sebastjan Trepca]

Hi,

sorry for late response and lack of details. Postgresql version is 8.2.5 .

This is how to reproduce this issue:

inh_test=# CREATE TABLE cities (
inh_test(# id serial,
inh_test(# nametext,
inh_test(# population  float,
inh_test(# altitudeint -- in feet
inh_test(# );
CREATE TABLE
inh_test=#
inh_test=# CREATE TABLE capitals (
inh_test(#   id serial,
inh_test(# nametext,
inh_test(# population  float,
inh_test(# altitudeint ,-- in feet
inh_test(# state   char(2)
inh_test(# ) ;
CREATE TABLE
inh_test=#
inh_test=# alter table capitals inherit cities;
ALTER TABLE
inh_test=# \d cities
Table public.cities
   Column   |   Type   |  Modifiers
+--+-
 id | integer  | not null default
nextval('cities_id_seq'::regclass)
 name   | text |
 population | double precision |
 altitude   | integer  |

inh_test=# \d capitals
Table public.capitals
   Column   |   Type   |   Modifiers
+--+---
 id | integer  | not null default
nextval('capitals_id_seq'::regclass)
 name   | text |
 population | double precision |
 altitude   | integer  |
 state  | character(2) |
Inherits: cities


[EMAIL PROTECTED] ~]$ pg_dump -c inh_test  inh_test.sql
[EMAIL PROTECTED] ~]$ psql -d inh_test  inh_test.sql
SET
SET
SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
DROP SEQUENCE
DROP SEQUENCE
DROP TABLE
DROP TABLE
DROP SCHEMA
CREATE SCHEMA
ALTER SCHEMA
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval

  1
(1 row)

CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval

  1
(1 row)

ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
[EMAIL PROTECTED] ~]$ psql -d inh_test
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

inh_test=# \d cities
Table public.cities
   Column   |   Type   |  Modifiers
+--+-
 id | integer  | not null default
nextval('cities_id_seq'::regclass)
 name   | text |
 population | double precision |
 altitude   | integer  |

inh_test=# \d capitals
   Table public.capitals
   Column   |   Type   |  Modifiers
+--+-
 id | integer  | not null default
nextval('cities_id_seq'::regclass)
 name   | text |
 population | double precision |
 altitude   | integer  |
 state  | character(2) |
Inherits: cities

inh_test=#


Capitals loses its own sequence in the second case.

Regards, Sebastjan

On 10/1/07, Tom Lane [EMAIL PROTECTED] wrote:
 Sebastjan Trepca [EMAIL PROTECTED] writes:
  Current state:

  Table B has a primary key with sequence b_seq. Table A also has a
  primary key with sequence a_seq.

 In view of the fact that primary keys aren't inherited, and do not
 have sequences, this description is uselessly imprecise.  Please show
 exactly how you created these two tables.  And which PG version is this?

 regards, tom lane


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Inheritance problem when restoring db

[Tom Lane]

Sebastjan Trepca [EMAIL PROTECTED] writes:
 This is how to reproduce this issue:
 ...
 inh_test=# alter table capitals inherit cities;

Fascinating.  pg_dump is almost smart enough to get this right, except
that what it spits out is

ALTER TABLE capitals ALTER COLUMN id SET DEFAULT 
nextval('capitals_id_seq'::regclass);
...
ALTER TABLE cities ALTER COLUMN id SET DEFAULT 
nextval('cities_id_seq'::regclass);

and since it already declared capitals as inheriting from cities, the
second command descends the inheritance tree and replaces the local
default for capitals.

What we apparently must do is add a dependency relation within pg_dump
to cause these two commands to be emitted in the other order.  I briefly
considered making these sorts of ALTERs be ALTER TABLE ONLY, but if we
go that route we will be unable to correctly restore the inherited vs
not-inherited property of default expressions.  (Not that the system
tracks that currently, but I think there were proposals on the table
to do so.)

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Inheritance foreign key unexpected behaviour

[Scott Ribe]

PostgreSQL foreign keys won't enforce restrictions the way you want them to;
you'll have to use a trigger. And at that point, you might as well consider
alternative designs...

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Inheritance foreign key unexpected behaviour

[Scott Marlowe]

On 10/20/07, M. van Egmond [EMAIL PROTECTED] wrote:
 Hi all,

 Im trying to use table inheritance in my database. I need it because i want
 to be able to link any object in the database to another. So i created a
 table my_object which has a serial, nothing more. All the other tables in
 the system are inherited from this my_object table. Im having difficulties
 adding foreign keys to the tables. This is my test setup:

From the inheritance docs at
http://www.postgresql.org/docs/8.2/static/ddl-inherit.html

A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply
to single tables, not to their inheritance children. This is true on
both the referencing and referenced sides of a foreign key constraint.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Inheritance foreign key unexpected behaviour

[M. van Egmond]

Hi all,

Im trying to use table inheritance in my database. I need it because i want
to be able to link any object in the database to another. So i created a
table my_object which has a serial, nothing more. All the other tables in
the system are inherited from this my_object table. Im having difficulties
adding foreign keys to the tables. This is my test setup:

PostgreSQL 8.2.5 on Windows

-- BEGIN OF SQL

CREATE TABLE my_object
(
  id serial NOT NULL,
  CONSTRAINT myobject_PK PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE my_child
(
  title text,
  CONSTRAINT child_PK PRIMARY KEY (id)
) INHERITS (my_object)
WITHOUT OIDS;

CREATE TABLE my_link
(
  foreign_object_id integer,
  CONSTRAINT link_PK PRIMARY KEY (id),
  CONSTRAINT link_FK_object FOREIGN KEY (foreign_object_id)
  REFERENCES my_object (id) MATCH SIMPLE
  ON UPDATE RESTRICT ON DELETE RESTRICT
) INHERITS (my_object)
WITHOUT OIDS;

INSERT INTO my_child(title) values('test object');

-- Now when i try to add a row to my_link referencing to the newly created
object in the my_child table and thus also available in the my_object table.

INSERT INTO my_link(foreign_object_id) values(1);

-- I get ERROR: insert or update on table my_link violates foreign key
constraint link_FK_object
-- SQL status:23503
-- Detail:Key (foreign_object_id)=(1) is not present in table my_object.

-- But if we do a simple select from the my_object table:

SELECT * FROM my_object WHERE id=1;

-- We do get the row.

-- END_OF_SQL


So what's wrong here? Is this improper use of the inheritance features or a
bug? Please help!

Thanks!

Matthieu van Egmond

Re: [GENERAL] Inheritance fixing timeline? (Was Inherited FK Indexing)

[Scott Ribe]

 Is this set to be fixed in any particular release?

Depending on what you're doing, this may be overkill, but: I have child
tables that not only need FK constraints, but also triggers and the
functions called by the triggers. So instead of writing this over and over
again, I eventually wrote a single procedure that takes the name of the
table, and using dynamic sql (execute command), generates the FKs and the
procedures and the triggers.

You *could* take it a step further, and have a procedure which takes the
name of the base table, finds all inherited tables, and makes sure
everything is set up correctly. I haven't, and probably won't, because I'm a
solo developer and don't make additions to the schema at such a great rate
that I would have trouble remembering to run my current FooChild_Setup
function on a new table.

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Inheritance problem when restoring db

[Sebastjan Trepca]

Hi,

I noticed a small bug/problem when restoring a database that uses inheritance.

Lets say you have a table B that inherits from table A.

Current state:

Table B has a primary key with sequence b_seq. Table A also has a
primary key with sequence a_seq.

Now we create a backup and restore the database.

New state:

Table B has a primary key with sequence a_seq. Table A is the same as before.

Is this wrong or normal functionality? Do I have to set some extra
flags when doing the backup?

Thanks, Sebastjan

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Inheritance problem when restoring db

[Tom Lane]

Sebastjan Trepca [EMAIL PROTECTED] writes:
 Current state:

 Table B has a primary key with sequence b_seq. Table A also has a
 primary key with sequence a_seq.

In view of the fact that primary keys aren't inherited, and do not
have sequences, this description is uselessly imprecise.  Please show
exactly how you created these two tables.  And which PG version is this?

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Inheritance fixing timeline? (Was Inherited FK Indexing)

[Alvaro Herrera]

Webb Sprague escribió:
 Is it
   possible to have  FK that spans into child tables?
 
  This is a well known (and documented, see [1]) deficiency. It's due to
  the current implementation of indices, which are bound to exactly one
  table, meaning they do return a position within the table, but cannot
  point to different tables.
 
 Is this set to be fixed in any particular release?

No, sorry.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Inheritance fixing timeline? (Was Inherited FK Indexing)

[Webb Sprague]

Is it
  possible to have  FK that spans into child tables?

 This is a well known (and documented, see [1]) deficiency. It's due to
 the current implementation of indices, which are bound to exactly one
 table, meaning they do return a position within the table, but cannot
 point to different tables.

Is this set to be fixed in any particular release?

(Here's hoping that someone smarter than I am is working on it...)

-W

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Inheritance and shared sequence

[Sebastjan Trepca]

Hi,

as I understood, when you create a table which inherits some other
table, the constraints and indexes do not go to the child table hence
you have to create a separate ones in there. That means you cannot
depend that you won't have duplicate IDs in both tables. Right?

BUT...what if child table and parent table both use the same sequence
for the primary key. Would the duplication still be an issue?

Thanks, Sebastjan

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Inheritance and shared sequence

[Richard Huxton]

Sebastjan Trepca wrote:

Hi,

as I understood, when you create a table which inherits some other
table, the constraints and indexes do not go to the child table hence
you have to create a separate ones in there. That means you cannot
depend that you won't have duplicate IDs in both tables. Right?

BUT...what if child table and parent table both use the same sequence
for the primary key. Would the duplication still be an issue?


Well, if you *always* use the sequence you'll be OK (until you run out 
of numbers), but it won't stop you manually supplying your own values.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Inheritance and shared sequence

[Oliver Elphick]

On Thu, 2007-06-07 at 09:44 +0100, Richard Huxton wrote:
 Sebastjan Trepca wrote:
  Hi,
  
  as I understood, when you create a table which inherits some other
  table, the constraints and indexes do not go to the child table hence
  you have to create a separate ones in there. That means you cannot
  depend that you won't have duplicate IDs in both tables. Right?
  
  BUT...what if child table and parent table both use the same sequence
  for the primary key. Would the duplication still be an issue?
 
 Well, if you *always* use the sequence you'll be OK (until you run out 
 of numbers), but it won't stop you manually supplying your own values.

If you must keep the primary key unique across a set of tables, you need
to create another table to index the keys and record which table each
key is in.  Use triggers to keep the index table up to date.
-- 
Oliver Elphick  [EMAIL PROTECTED]
Isle of Wight  http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
 
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Inheritance question

[Andrew Sullivan]

On Thu, May 24, 2007 at 04:44:53PM -0700, Raymond C. Rodgers wrote:
 The question, and point, is this: Is there an alternate way of 
 accomplishing read and write functionality similar to what inheritance 
 offers but allowing me to map the columns as I desire? The read only 

It isn't clear from your question whether the data in the tables is
_the same_ in these cases.  If so, then you could have various child
tables inherit the parent, and the parent would be the largest of all
the tables (the child tables without the columns in question would
just have those columns null).  But in general, my impression is that
you don't want inheritance here.  What you probably want is better
normalization of your data on the way in.

A


-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] Inheritance question

[Raymond C. Rodgers]

First, I want to confess that I am not an SQL expert or even remotely 
close. :-)
Second, I believe I pretty much know the answer to my question, but I 
would like to have some confirmation if you fine people don't mind.


My situation is this: I have a PHP script that some what dynamically 
generates two or more tables (but for the sake of this email lets stick 
with two), and a view to display the results of those tables. The tables 
might be defined like this:


Table A
==
id bigint not null (Primary Key)
b0 int not null
b1 int not null
b2 int not null

Table B
==
id bigint not null (Primary Key)
b0 int not null
b1 int not null
b2 int not null
b3 int not null

And the view ultimately maps those tables in combination with Table C 
which contains a serial column that the id in Tables A  B use. The view 
might have been created like this:


create view View1 as select c.id, a.b0 as r0, a.b1 as r1, a.b2 as r2, 
b.b0 as r3, b.b1 as r4, b.b2 as r5, b.b3 as r6 from TableC c left join 
TableA a on c.id = a.id left join TableB b on c.id = b.id


This results in a virtual table/view that contains all of the columns 
from tables A  B, in addition to another column from table C. That's 
fine for most of my purposes.


I only recently learned about the inheritance features, and I was hoping 
that I could find a way to implement a table that is the child of tables 
A  B that would allow me to map the columns as I desire rather than 
automatically merging them. Based on the documentation and my own 
testing, I would have to say that this isn't possible in 8.1 (which I 
currently use) or 8.2 (which I'll be moving to soon).


The question, and point, is this: Is there an alternate way of 
accomplishing read and write functionality similar to what inheritance 
offers but allowing me to map the columns as I desire? The read only 
view is nice, but it would save me a lot of work if I could take 
advantage of inheritance in this way.


Thanks for your time and patience!
Raymond

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Inheritance question

[Raymond C. Rodgers]

First, I want to confess that I am not an SQL expert or even remotely
close. :-)
Second, I believe I pretty much know the answer to my question, but I
would like to have some confirmation if you fine people don't mind.

My situation is this: I have a PHP script that some what dynamically
generates two or more tables (but for the sake of this email lets stick
with two), and a view to display the results of those tables. The tables
might be defined like this:

Table A
==
id bigint not null (Primary Key)
b0 int not null
b1 int not null
b2 int not null

Table B
==
id bigint not null (Primary Key)
b0 int not null
b1 int not null
b2 int not null
b3 int not null

And the view ultimately maps those tables in combination with Table C
which contains a serial column that the id in Tables A  B use. The view
might have been created like this:

create view View1 as select c.id, a.b0 as r0, a.b1 as r1, a.b2 as r2,
b.b0 as r3, b.b1 as r4, b.b2 as r5, b.b3 as r6 from TableC c left join
TableA a on c.id = a.id left join TableB b on c.id = b.id

This results in a virtual table/view that contains all of the columns
from tables A  B, in addition to another column from table C. That's
fine for most of my purposes.

I only recently learned about the inheritance features, and I was hoping
that I could find a way to implement a table that is the child of tables
A  B that would allow me to map the columns as I desire rather than
automatically merging them. Based on the documentation and my own
testing, I would have to say that this isn't possible in 8.1 (which I
currently use) or 8.2 (which I'll be moving to soon).

The question, and point, is this: Is there an alternate way of
accomplishing read and write functionality similar to what inheritance
offers but allowing me to map the columns as I desire? The read only
view is nice, but it would save me a lot of work if I could take
advantage of inheritance in this way.

Thanks for your time and patience!
Raymond


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] inheritance

[Bruno Wolff III]

On Tue, Dec 19, 2006 at 11:20:35 +0100,
  Udo Zubel [EMAIL PROTECTED] wrote:
 Hi Im engineering a PG database with my workmate.
 Unfortunately the inheritance feature seems not to be able to solve my 
 problem.
 I have a table order with an orderID, each order has 1 to n types of 
 articles, like services and supply articles with the same PK. 
 The PK is order_number and position, the PK must be unique over all 
 article-tables. All articles have 1 to n packages. 
 I have a table art as the mother-table. All article-tables inherit the PK 
 and some other columns from that table. 
 My problem is now that i dont know how i can make the PK on art unique and 
 make a FK from packages to art.
 
 At the moment im thinking about a workaround using triggers, but i wanted to 
 know some other opinions on that matter first.

Is there a table of valid articles?

If so, then the mother table should have foreign key references to order and
the article tables and both of these references should be declared as the
primary key.

If not, and you can use any old numbers for the article, then you might want
use a sequence for the article number. If you do this, these will be unique
in themselves, so that in combination with the order_number they will still
be unique. And doing things this way will be a lot simpler than trying to
maintain a separate pool for each order_number.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] inheritance

[Udo Zubel]

Hi Im engineering a PG database with my workmate.
Unfortunately the inheritance feature seems not to be able to solve my problem.
I have a table order with an orderID, each order has 1 to n types of 
articles, like services and supply articles with the same PK. 
The PK is order_number and position, the PK must be unique over all 
article-tables. All articles have 1 to n packages. 
I have a table art as the mother-table. All article-tables inherit the PK and 
some other columns from that table. 
My problem is now that i dont know how i can make the PK on art unique and make 
a FK from packages to art.

At the moment im thinking about a workaround using triggers, but i wanted to 
know some other opinions on that matter first.

Thanks in advance,

Mario



-
NEU: Fragen stellen - Wissen, Meinungen und Erfahrungen teilen. Jetzt auf 
Yahoo! Clever.

[GENERAL] inheritance and index use (similar to UNION ALL)

[Karsten Hilbert]

Hi,

we have a parent table root_item with a few common fields
(one is a text field) from which a whole bunch of child
tables derives.

We need to run queries against the text field across the
whole bunch of child tables. What naturally comes to mind is
to run the query against root_item.text_field thereby
catching all child table text_field values as well.

However, the planner doesn't really seem to consider indices
of the parent table. It was said that 8.2 would be making
improvements related to this and the Release Notes do have a
comment on planner improvements for UNION/inherited tables.
And, yes, the data does warrant using indices over using seq
scans. Explicitely joining the subtables one by one yields
orders of magnitude faster results (10 minutes going down to
2 seconds) and uses indices.

What I am wondering is:

 Should this really work (better) in 8.2 ?

 Do I need to provide more data (schema, explain plan etc) ?

 Am I doing something wrong (apart from perhaps chosing a
 non-performant schema design) ?

Thanks,
Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] inheritance and index use (similar to UNION ALL)

[Karsten Hilbert]

Further testing has revealed that, indeed, PG 8.2 speeds up
our use of child tables !

The query in question went down from 10 minutes to *under a
second* just by running against 8.2  :-)

Now, that's some gain !

Thanks to the PostgreSQL developers.

Karsten,
GNUmed team


On Sun, Dec 10, 2006 at 09:43:35AM +0100, Karsten Hilbert wrote:
 Subject: [GENERAL] inheritance and index use (similar to UNION ALL)
 User-Agent: Mutt/1.5.13 (2006-08-11)
 
 Hi,
 
 we have a parent table root_item with a few common fields
 (one is a text field) from which a whole bunch of child
 tables derives.
 
 We need to run queries against the text field across the
 whole bunch of child tables. What naturally comes to mind is
 to run the query against root_item.text_field thereby
 catching all child table text_field values as well.
 
 However, the planner doesn't really seem to consider indices
 of the parent table. It was said that 8.2 would be making
 improvements related to this and the Release Notes do have a
 comment on planner improvements for UNION/inherited tables.
 And, yes, the data does warrant using indices over using seq
 scans. Explicitely joining the subtables one by one yields
 orders of magnitude faster results (10 minutes going down to
 2 seconds) and uses indices.
 
 What I am wondering is:
 
  Should this really work (better) in 8.2 ?
 
  Do I need to provide more data (schema, explain plan etc) ?
 
  Am I doing something wrong (apart from perhaps chosing a
  non-performant schema design) ?
 
 Thanks,
 Karsten
 -- 
 GPG key ID E4071346 @ wwwkeys.pgp.net
 E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
 
 ---(end of broadcast)---
 TIP 3: Have you checked our extensive FAQ?
 
http://www.postgresql.org/docs/faq

-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Inheritance and unique constraints

[Christian Rengstl]

Hi everyone,

i hope (and i'm sure) somebody can answer my question: if i have a master table 
and several child tables, do the child tables inherit the unique 
constraint(s) defined for the master table or do i have to define the same 
constraints for the same fields in all the child tables?

Thanks!

--
Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Inheritance and unique constraints

[John Purser]

On Wed, 05 Jul 2006 15:51:23 +0200
Christian Rengstl [EMAIL PROTECTED] wrote:

 Hi everyone,
 
 i hope (and i'm sure) somebody can answer my question: if i have a
 master table and several child tables, do the child tables inherit
 the unique constraint(s) defined for the master table or do i have to
 define the same constraints for the same fields in all the child
 tables?
 
 Thanks!
 
 --
 Christian Rengstl M.A.
 Klinik und Poliklinik für Innere Medizin II
 Kardiologie - Forschung
 Universitätsklinikum Regensburg
 B3 1.388
 Franz-Josef-Strauss-Allee 11
 93053 Regensburg
 Tel.: +49-941-944-7230
 
 
 ---(end of
 broadcast)--- TIP 9: In versions below 8.0,
 the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

As of 8.1.0 UNIQUE constraints are not inherited.  See page 62 of the
documentation.

John Purser

-- 
Courage is your greatest present need.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] inheritance and table

[nik600]

ok i have understand

many thanks to all!

On 6/27/06, Erik Jones [EMAIL PROTECTED] wrote:

nik600 wrote:
 hi

 i don't have understand how works inheritance of tables...

 if table B inherits from table A

 - A and B must share primary keys?

 - if i isert record in B the record is replaced in A ?

 can i avoid this?

 i would like to have this scenario:

 table A

 table B inheridts from A
 table C inheridts from A

 if i insert a record in B it must be insered only in B!
 if i insert a record in C it must be insered only in C!

 is it possible?

 thanks
Do you mean like this?  (Notice the use of LIKE instead of INHERITS):

CREATE TABLE table_1 (
a int,
b int
)

CREATE TABLE table_2 (
LIKE table_1
)

(**Note:  CREATE TABLE with INHERITS uses different syntax!**)

INSERT INTO table_1 (a, b) VALUES (1, 2);
INSERT INTO table_2 (a, b,) VALUES (3,  4);

Now,

SELECT * FROM table_1;

yeilds,

_a | b_
1 | 2

and not,

_a | b
_1 | 2
3 | 4

as it would've if you'd used INHERITS instead of LIKE.

--
erik jones [EMAIL PROTECTED]
software development
emma(r)




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] inheritance and table

[nik600]

hi

i don't have understand how works inheritance of tables...

if table B inherits from table A

- A and B must share primary keys?

- if i isert record in B the record is replaced in A ?

can i avoid this?

i would like to have this scenario:

table A

table B inheridts from A
table C inheridts from A

if i insert a record in B it must be insered only in B!
if i insert a record in C it must be insered only in C!

is it possible?

thanks

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] inheritance and table

[Richard Broersma Jr]

 i don't have understand how works inheritance of tables... 
 if table B inherits from table A
 - A and B must share primary keys?

No, currently there is no unique constraint that will force uniqueness across 
parent/child/sibling
tables.  Just think of them as being nothing more than seperate table that 
share simlar
data-definitions.

When you select * from parent;  you are essentially preforming a:

select * from parent
union
select * from childa
union
select * from childb ;

if you want to only see the records in A then select * from only parent;

 - if i isert record in B the record is replaced in A ?
 
 can i avoid this?

This will not happen, you will end up with two records one A and one in B.

 
 i would like to have this scenario:
 
 table A
 
 table B inheridts from A
 table C inheridts from A
 
 if i insert a record in B it must be insered only in B!
 if i insert a record in C it must be insered only in C!

This is how is it will work.

http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html
Notice 5.8.1. Caveats

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] inheritance and table

[Erik Jones]

nik600 wrote:

hi

i don't have understand how works inheritance of tables...

if table B inherits from table A

- A and B must share primary keys?

- if i isert record in B the record is replaced in A ?

can i avoid this?

i would like to have this scenario:

table A

table B inheridts from A
table C inheridts from A

if i insert a record in B it must be insered only in B!
if i insert a record in C it must be insered only in C!

is it possible?

thanks

Do you mean like this?  (Notice the use of LIKE instead of INHERITS):

CREATE TABLE table_1 (
   a int,
   b int
)

CREATE TABLE table_2 (
   LIKE table_1
)

(**Note:  CREATE TABLE with INHERITS uses different syntax!**)

INSERT INTO table_1 (a, b) VALUES (1, 2);
INSERT INTO table_2 (a, b,) VALUES (3,  4);

Now,

SELECT * FROM table_1;

yeilds,

_a | b_
1 | 2

and not,

_a | b
_1 | 2
3 | 4

as it would've if you'd used INHERITS instead of LIKE.

--
erik jones [EMAIL PROTECTED]
software development
emma(r)


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] inheritance and table

[arie nugraha]

Inheritance in postgre means you will have same fields definition
like the inherited table plus its own fields. So if table B is inherit 
table A,
table B will have same field  definition  like A plus  table  B own 
unique field(s).


It wont share primary keys, table B just have primary key in the same 
field(s) like A
and records in table A wont be replaced by record(s) inserted to table B 
or vice versa.


table A and table B basically a different entity, they just have same 
fields definition.


Hope that will help you


hi

i don't have understand how works inheritance of tables...

if table B inherits from table A

- A and B must share primary keys?

- if i isert record in B the record is replaced in A ?

can i avoid this?

i would like to have this scenario:

table A

table B inheridts from A
table C inheridts from A

if i insert a record in B it must be insered only in B!
if i insert a record in C it must be insered only in C!

is it possible?

thanks

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Inheritance

[Richard Broersma Jr]

 Jebus scrisse in data 03/27/06 19:03:
 
 I could be wrong but primary keys and other constraints are not inherited.
do you know if 
 this problem is solved in postgres 8.1?

No it isn't.  But I remember reading on one of the lists that it was on the 
to-do list for 8.2. 
However, I do not know how high it is on the list of things to do.  So I 
imagine that there is the
potential that it might not be added.

Regards,

Richard Broersma Jr.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Inheritance

[Paolo Sala]

Richard Broersma Jr scrisse in data 03/28/06 15:18:

No it isn't.  But I remember reading on one of the lists that it was on the to-do list for 8.2. 
However, I do not know how high it is on the list of things to do.  So I imagine that there is the

potential that it might not be added.


I'll wait 8.2 or 8.3.

Thank you very much.

Piviul

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Inheritance

[Paolo Sala]

Hi, I'm new on postgres and I've just installed postgres 7.4.7 on a 
debian sarge. I'm interested on using inheritance. I've tried a simple code:


CREATE TABLE t_main (
   id  serial  primary key
);

CREATE TABLE t_derived1 (
   field1  varchar(128)default NULL
) INHERITS (t_main);

Now I have to create another table having a field having a reference to 
t_derived1. If I use the code

CREATE TABLE t_table1 (
   id  serial  primary key,
   id_derived1 int references t_derived1
);
I got an error:  t_derived1 have no primary key... and in effect is 
t_main that have the primary key... So I modified the code in

CREATE TABLE t_table1 (
   id  serial  primary key,
   id_derived1 int references t_main
);
and now all seems to work so I inserted a record on t_derived1

INSERT INTO t_derived1
   (field1) VALUES ('field1 content of derived1 table');

and a record in t_table1 that have a reference to the record I've just 
inserted:


INSERT INTO t_table1
   (id_derived1) VALUES (1);

but I've got the error 'ERROR:  insert or update on table t_table1 
violates foreign key constraint $1

DETAIL:  Key (id_derived1)=(1) is not present in table t_main.'

So I ask you: there is a way to reference a record to an hinherited table?

Thank you very much

Piviul




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] Inheritance

[Paolo Sala]

Jebus scrisse in data 03/27/06 19:03:


I could be wrong but primary keys and other constraints are not inherited.
 

Thank you very much Jebus; in effect I've found in the mailing list 
archives a 2003 thread INHERITS and Foreign keys that claim the same 
problem. Someone (Stephan Szabo) answered saying that At some point in 
the future, that's likely to change 
(http://archives.postgresql.org/pgsql-sql/2003-12/msg00101.php). Now I'm 
using postgres 7.4.7 and I've found the same problem; do you know if 
this problem is solved in postgres 8.1?


Thank you very much

Piviul

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Inheritance Algebra

[Karsten Hilbert]

On Thu, Dec 22, 2005 at 05:05:49PM -0700, Trent Shipley wrote:

 On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote:

  I would assume quite a few people would use table
  inheritance in a simple way were it available in a more
  convenient fashion: to transport fields, primary and foreign
  keys to child tables.
 
 I am not clear on why this sort of scenario benefits more from CREATE TABLE's 
 INHERITS clause than the LIKE clause
Because the inherited fields are aggregated in the parent
table.

Imagine a database:

create table narrative_base (
narrative text
);

create table memo (
author text default CURRENT_USER
) inherits (narrative_base);

create table ads (
fk_campaign integer references campaigns(pk)
) inherits (narrative_base);

... more child tables

... even more child tables

Then we go on merrily inserting all sorts of stuff into the
narrative_base child tables for two years.

Now the boss asks me: Has anyone ever written anything with
'PostgreSQL' in it in our company ?

So I go

 select tableoid, * from narrative_base where narrative ilike '%postgresql';

et voila. I don't have to remember all the tables
potentially containing narrative and join them.

Now, if this properly transporter primary and foreign keys
to child tables I could add

pk serial primary key

to narrative_base and be done with primary keys for all
children.

Get the drift ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Inheritance Algebra

[Mike Rylander]

On 12/23/05, Karsten Hilbert [EMAIL PROTECTED] wrote:
 On Thu, Dec 22, 2005 at 05:05:49PM -0700, Trent Shipley wrote:

  On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote:

   I would assume quite a few people would use table
   inheritance in a simple way were it available in a more
   convenient fashion: to transport fields, primary and foreign
   keys to child tables.
 
  I am not clear on why this sort of scenario benefits more from CREATE 
  TABLE's
  INHERITS clause than the LIKE clause
 Because the inherited fields are aggregated in the parent
 table.

 Imagine a database:

 create table narrative_base (
 narrative text
 );

 create table memo (
 author text default CURRENT_USER
 ) inherits (narrative_base);

 create table ads (
 fk_campaign integer references campaigns(pk)
 ) inherits (narrative_base);

 ... more child tables

 ... even more child tables

We use something very similar to this to track user transactions
(circulation of material, billings, etc.) in our (developing) ILS
(Integrated Library System), OpenILS.  But we take it even further
with multiple levels of inheritance (simplified):

CREATE TABLE payment (
   pid serial,
   xact bigint,
   ptime timestamptz,
   pamount numeric(10,2)
);

CREATE TABLE bnm_payment ( -- brick-n-mortar
   accepting_user int
) INHERITS (payment);

CREATE TABLE bnm_desk_payment (
   cash_drawer_id text
) INHERITS (bnm_payment);

CREATE TABLE check_payment (
   check_number text
) INHERITS (bnm_desk_payment);

... and so on ...


 Then we go on merrily inserting all sorts of stuff into the
 narrative_base child tables for two years.

 Now the boss asks me: Has anyone ever written anything with
 'PostgreSQL' in it in our company ?

 So I go

  select tableoid, * from narrative_base where narrative ilike '%postgresql';

 et voila. I don't have to remember all the tables
 potentially containing narrative and join them.


Precisely.  We can report on daily payments at each of the levels
all the way down to payment type, or just get a total for the cash
drawers, or a grand total.  Billing line items are structured
similarly, so it's also very easy to grab a summary bill for a user
and explode it for a detailed view using tableoid.


 Now, if this properly transporter primary and foreign keys
 to child tables I could add

 pk serial primary key

 to narrative_base and be done with primary keys for all
 children.

 Get the drift ?

While I originally wanted this as well, by using a serial for the
pid field in the root table you've essentially go that.  While
cross-table unique indexes aren't available now, I know that some
smart people are thinking about them.  Most of the time it comes up in
relation to O*'s global indexes on partitioned tables, and in that
sense is not of much use due to performance implications, but I think
/our/ use makes a strong case for such a beast.

That said, I believe I have a workaround that may suffice if you
absolutely require constraint enforced globally unique PKEYs.  This
example uses the pid field from the root table (that is inherited
everywhere) to track uniqueness.

CREATE TABLE payment_entities (
  id bigint primary key,
  toid oid  -- tableoid
);

CREATE FUNCTION global_unique_payment_entity RETURNS TRIGGER AS $$
  BEGIN
BEGIN
  insert into entities (id, toid) values (NEW.pid, TG_RELID);
EXCEPTION
  WHEN UNIQUE_VIOLATION THEN
RAISE EXCEPTION 'Ack!  Key % already exists as a payment ID', NEW.pid;
  END;
RETURN NEW;
  END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER global_unique_entity_payment_trig
  BEFORE INSERT ON cash_payment
FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity();

CREATE TRIGGER global_unique_entity_payment_trig
  BEFORE INSERT ON check_payment
FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity();

CREATE TRIGGER global_unique_entity_payment_trig
  BEFORE INSERT ON credit_card_payment
FOR EACH ROW EXECUTE PROCEDURE global_unique_payment_entity();

etc...

That doesn't cover UPDATEs of course, but that should be easy enough
to do. It does, however, give you a simple type lookup table if you
happen to have a pid in hand and want to know what it is.

Thoughts?


 Karsten
 --
 GPG key ID E4071346 @ wwwkeys.pgp.net
 E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

 ---(end of broadcast)---
 TIP 5: don't forget to increase your free space map settings



--
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Inheritance Algebra

[Trent Shipley]

On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote:
 On Wed, Dec 21, 2005 at 01:52:34PM +0100, Martijn van Oosterhout wrote:
  On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote:
   Relational Constraint Inheritance Algebra
   With regard to class and attribute uniqueness
 
  It's taken a while to digest this and sorry for the delay. While I find
  the ideas intreguing there is a little voice in the back of my head
  asking: practical applications?

 I would assume quite a few people would use table
 inheritance in a simple way were it available in a more
 convenient fashion: to transport fields, primary and foreign
 keys to child tables.

I am not clear on why this sort of scenario benefits more from CREATE TABLE's 
INHERITS clause than the LIKE clause (assuming that LIKE copied the 
appropriate table properties).  Indeed, the recursive SELECT associated with 
INHERITS might be undesirable.

If I understand you [Karsten] correctly then the really elegant way to do this 
is with a DECLARE or 
DEFINE TABLE|INDEX|FOREIGN KEY|... definition_name (definition_clause)

(The choice of DECLARE or DEFINE would depend on the SQL list of reserved 
words.) 

Then instantiate the declared object with something like:
CREATE TABLE|INDEX|... object_name USING definition_name.

Changes in definition (ALTER DEFINITION)should optionally cascade to 
instantiated objects.  Use ALTER TABLE to create variant tables.  Very useful 
for creating things that often get quashed and re-created, like temporary 
tables and indexes.  Also very useful for things that should be uniform but 
get attached to many tables, like annoying ubiquitous check constraints, 
indexes, or foreign keys.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Inheritance Algebra

[Martijn van Oosterhout]

On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote:
 Relational Constraint Inheritance Algebra
 With regard to class and attribute uniqueness

snip

It's taken a while to digest this and sorry for the delay. While I find
the ideas intreguing there is a little voice in the back of my head
asking: practical applications?

For programming, inheritance provides a way of reusing code in a way
that encapsulates changes. But I have yet to find a lot of data that
really needs this kind of encapsulation. I think one of the reason
inheritance hasn't had a lot of work done in PostgreSQL is because the
use-cases aren't compelling enough to make someone want to put the
effort in.

Indeed, most data is structured such that you have a unique key and
various attributes associated with that. What SQL excels at it joining
tables on those keys. The uniqueness or otherwise of non-key fields is
not generally important.

The only situation I've come across inheitence being truly useful would
be where you have several different services which are associated
with a customer but each require different services. But even then, the
inheritence would only be useful if code utilizing it is within the
backend. As soon as the data is transferred to the application, *that*
is where the inheritence hierarchy is and it no longer cares if the
inheritence is present in the database itself.

That's my 2c anyway...
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpuM5G2h4evb.pgp
Description: PGP signature

Re: [GENERAL] Inheritance Algebra

[Karsten Hilbert]

On Wed, Dec 21, 2005 at 01:52:34PM +0100, Martijn van Oosterhout wrote:

 On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote:
  Relational Constraint Inheritance Algebra
  With regard to class and attribute uniqueness

 It's taken a while to digest this and sorry for the delay. While I find
 the ideas intreguing there is a little voice in the back of my head
 asking: practical applications?
I would assume quite a few people would use table
inheritance in a simple way were it available in a more
convenient fashion: to transport fields, primary and foreign
keys to child tables.

In GNUmed (a medical practice application)
 http://salaam.homeunix.com/twiki/bin/view/Gnumed/WebHome
we use inheritance to make tables inherit

a) audit fields
b) common clinical fields such as a pointer to the patient

We overcome the primary/foreign key problem by a) letting
child tables have their own primary key which is quite
useful anyways and b) re-declaring foreign keys on child
tables.

While using inheritance isn't strictly necessary it is quite
convenient and makes the schema more intuitive.

There's also one major gain: since all clinical child tables
store their unstructured narrative in a field provided by
the clin_root_item parent table doing a search across the
entire narrative of the medical record is a simple query
against one table.

http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/?root=gnumed

(see gmAudit.sql and gmclinical.sql)

 The only situation I've come across inheitence being truly useful would
 be where you have several different services which are associated
 with a customer but each require different services.
Yes, this is similar to what we do.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] inheritance/foreignkeys

[jef peeraer]

i have a project where inheritance could be used, i think. but after 
going through the lists and having read the FM's, i realize that i beter 
use another approach. i just describe the project in short :


users have to fill in forms, that mainly exists of different lists 
and/or textboxes. Users are part of teams ( kind of groups)  , and every 
team is allowed to have its extra listitems, which should be added to 
the global lists ( as i said, this could be solved with inheritance...). 
   The teams should be able to insert their extra listitems in a table, 
without touching the global listitems. This could be solved by using a 
different schema for each team, and each team has its own listitems 
table. The selected items will end up in a registration table, also 
specific per team.


public.listitems
  id serial
  id_up int
  priority int2
  label text


teamX.listitems
  like public.listitems

teamY.listitems
  like public.listitems

teamX.registration
  reg_id serial
  time_registration date
  list_id   foreign 

i can create the composed listitems for a team by using a select and 
UNION of the two tables. I am a little bit stuck how i should implement 
the foreign key in the registration table as the list_id can reference a 
listitem of the public schema as well of the teams schema.


1-maybe i should use triggers, and all listiteems inserts by the teams 
should be inserted in the public.listitems as well

2- create a new table that consists of union of both tables
3- other solution

jef peeraer

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Inheritance Algebra

[Karsten Hilbert]

Trent,

although I cannot contribute much of anything to your line
of thought I'd encourage you to keep on with it as it'd be
highly desirable (for GNUmed at least) to have a stronger/
more encompassing inheritance solution in PostgreSQL.

Karsten,
GNUmed developer
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Inheritance Algebra

[Trent Shipley]

[ 
This post is theory oriented, so it can't go in HACKERS
nor can it go in SQL 
so it gets posted to GENERAL.

I would polish this more.  Unfortunately, it is at the point were I'd seek 
feedback were I in a seminar.
]

Relational Constraint Inheritance Algebra
With regard to class and attribute uniqueness

 0 Intro: Postgresql inheritance and uniqueness

Postgresql's INHERITS is one of the most intriguing features of the 
at-liberty, open-source database.  At the same time, most observers regard 
INHERITS as an incomplete implementation of a fully object-oriented, or 
better, class-aware, database function.  The most glaring omission is that 
primary key and unique constraints are not inherited by children.

Nevertheless, the implementation of INHERITS has not changed much through 
the 
last several revisions of Postgresql.  Bizgres' partitioning scheme, 
constraint based exclusion [?], relies on the current default behavior of 
inheritance in Postgresql.  No doubt other consumers have taken advantage of 
the feature's current behavior, so any extension must preserve existing 
behavior by either developing sub-clauses that further specify the behavior 
of the INHERITS or they must develop an entirely new lexis for building 
inheritance based relational classes.

When a constraint is declared in a database that supports relational 
inheritance, the constraint necessarily has scope.  In the simplest cases, 
constraint scope is local, applying only to the table where the constraint 
was declared, or the scope is to the subclass, applying to this table and all 
descendants unless over-ridden.  According to the Postgresql 8.0 
documentation, all constraints are automatically inherited unless over-ridden 
(the subclass model) except for foreign and unique constraints that are 
unsupported at the class level.  In effect, under Postgresql 8.0 foreign and 
unique constraints have local scope.
 
Another notable quirk of Postgresql's inheritance model is that no table is 
explicitly aware it could become a parent.  There is no “abstract” or “final” 
clause nor any other clause restricting the behavior or potential children 
exists in “CREATE TABLE”.  Indeed, the top of any inheritance hierarchy 
necessarily begins as a strictly relational table.  One side effect of the 
current model is that implementing class-wide uniqueness is problematic.  
Either the parent model would need to be abstract (a nonexistent clause) or a 
child's inheritance of a unique constraint would change the behavior of the 
parents heretofore table-local unique (or even non-unique) column.

Postgresql's current hybrid implementation of inheritance, having both 
implicitly local and subclass scope for  different kinds of constraints, 
points to a powerful hybrid model where columns can have   constraints that 
are explicitly declared with table-local or subclass-wide scopes.

The rest of this essay examines the interaction of localism-class cross 
plurality-uniqueness[1].  It seems obvious that the distinctions have 
theoretical discussion (and hopefully acceptance).  More important is whether 
the supporting these distinctions would be useful in any real-world product.  
I believe that supporting such fine distinctions would be of some use, but 
will make no further effort to argue the case.

 1 Types of relational inheritance models 

Relational inheritance of a constraint feature has scope [2].  Levels of scope 
include absent (necessarily local), table-local, subclass, class-wide, mixed, 
and dual.  
Obviously, support for relational inheritance can simply be absent.  This is 
the norm.  Any  such table is strictly relational and all constraints are 
necessarily local.  Tables in this essay are explicitly not under the 
“absent” relational inheritance scope.

Another family of models for relational inheritance scope might be called 
local (table-local or relation-local).  If Postgresql's CREATE TABLE ... LIKE 
clause allowed for “inheritance” of all constraints, triggers, and so on, it 
would be an implementation of the local model.  In particular, unique 
constraints are checked for each table in the class but are not enforced over 
the whole of an entire class or subclass.  Presumably, if table-local scope 
were the default behavior across a database, queries would not recurse into 
descendant tables by default.  Note that this used to be Postgresql's default 
behavior.  SQL developers had to ask the engine to recurse into descendant 
tables.

Mixed scope models extend the local model, allowing for class-like treatment 
of some relational aspects.  (In this essay we are particularly concerned 
with plurality-uniqueness.)  Arguably (and unfortunately), Postgresql 
currently implements a mixed model.  Some constraints have subclass scope and 
some have local scope.

A traditional, strictly hierarchical inheritance of constraints from 
object-aware tables by descendants is a powerful scoping model.   Strictly 
speaking, every table 

Re: [GENERAL] Inheritance vs. LIKE - need advice

[Jeff Davis]

William Bug wrote:
 
 
 I'm not certain I understand what you mean here?  Are you  recommending
 all application layer interaction with tables using  INHERIT should be
 done via a VIEW intermediary?  If so, wouldn't the  VIEW (built from a
 SELECT ... ONLY...) then be as dependent on the  fixed structure
 determined by the INHERITs relationship, as much as  the application
 code would be?
 

Well, what I'm concerned about is this: you have an inheritance
hierarchy in PG, and some application has a SELECT ... ONLY in it. If
you want to change the inheritance hierarchy in PG around, you may not
be able to make it look like the old hierarchy to the application with
views.

If you use a view in between, maybe the view does the SELECT ... ONLY.
That way, if you change the PG inheritance hierarchy, you can just
change the view without changing the application. The application would
never use SELECT ... ONLY so you would always have a way out if you
need it.

My basic philosophy here is that as long as you have a way out, it's not
 wrong.

Regards,
Jeff Davis

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Inheritance vs. LIKE - need advice

[Jeff Davis]

William Bug wrote:
 As you say, both LIKE  INHERIT are a bit anemic and unnecessary,  since
 what they provide can be implemented via VIEWs and RULEs/ TRIGGERs.  I'd

I'd like to point out that INHERITS is unique, although I'm not sure all
of the exact differences. The main difference that I see is that SELECT
reads from multiple tables, and to make it only read one you do SELECT
... ONLY.

LIKE is purely syntactic sugar. Use it whenever it saves time and/or
reduces confusion.

 VIEWs, at least).  Being able to create  MATERIALIZED VIEWs would
 probably help to remove any realtime  performance issues introduced by
 requiring additional JOINs to  harvest this view of the data.  I can do
 this myself via RULEs/ TRIGGERs or use the system being developed as an
 extension to Pg -  The matview Project
 (http://gborg.postgresql.org/project/matview/ projdisplay.php).

Agreed. Materialized views are a great way to get whatever performance
benefits you need from physical representation without imposing on the
logical layout.

For some reason a good document on the subject is on a server that's not
responding right now (hopefully temporarily!). Here's the google cache:
http://66.102.7.104/search?q=cache:jaaXngt0hioJ:jonathangardner.net/PostgreSQL/materialized_views/matviews.html+materialized+view+postgresqlhl=en

 I actually think the INHERITs  LIKE features of PostgreSQL - in  their
 simplicity - potentially offer a better means to providing  Object
 properties to SQL relations, than the more complex, fully  realized
 Object-Relational systems, which are inherently better  suited to the
 task of providing a seamless persistence mechanism for  OOP code
 objects.  I do hope its not true Pg's INHERIT  LIKE  features are
 vestigial and will continue to be developed into the  future.  in the

I think I overstated what I meant in my previous email... it should be
more like it's in a stasis rather than on life support. When PG
solves some of the table partitioning issues in future releases, you can
bet that those features will help complete the inheritance model. After
a while, INHERITS will also be merely a syntax for capabilities that are
available otherwise.

 meantime, in light of what you say regarding the lack  of active
 development and maintenance they are receiving, I'll  probably lay off
 using them much for now.  As you say, heavy use of  INHERITs given the
 current support given to this feature is more a  liability than a
 convenience at this point.

Not too many people use INHERITS. I think it's fairly independent in the
code and probably not too many bugs appear, but if a bug creeps in, the
limited testing might not catch it. Maybe a brief scan of the -bugs list
might indicate whether inheritance is a source of problems, or benign
syntax that primarily depends on other database features which are
well-tested.

 
 I wasn't clear about this in that initial post, but my references to 
 OOP techniques were just by way of analogy.  I wasn't really asking  the
 question with a thought toward creating a model to mirror my OOP  models
 - to be simply a persistence mechanism.  There are many  wonderful

It was mainly just a warning that sometimes application algorithms tend
to mix with the data model.

 So - the real point I was trying to make is:
 1) INHERIT  OOP inheritance: each provide a relatively  efficient
 means to model real-world objects with inherent parent- child,
 hierarchical relations to one another;

True, many real-world entities have the famous isa relationship, and
that's valueable to take into account in the data model.

 2) LIKE  Interfaces(Java)/Mixins(Ruby): each provide a means to 
 share a set of pre-defined attributes/methods amongst a set of class 
 that otherwise have no inherent hierarchical relation to one another.
 
 You are certainly correct - Interfaces (Java) are essentially a 
 compile-time enforcement mechanism.  Changes you make to an Interface 
 after it has been used to generate bytecodes (i.e., compile) for a  Java
 class will not have any effect on the class definition until you 
 compile it again.  Interfaces provide a fairly simple - but effective  -
 means to an end - for Java to provide for inheriting aspects from 
 more than one existing code entity without supporting multiple 
 inheritance (which Pg actually does support).  Mixins in Ruby, on the 
 other hand, don't really get added to a class until runtime (though 
 they are used at compile time to resolve function  variable calls).  
 If you add to a Mixin, you could actually use that new feature next 
 time you use a class whose definition file included that Mixin.  This 
 is largely due to the fact that Ruby is interpreted at runtime and is  a
 very loosely typed language.

Definitely a tangent, but I think most people would consider Ruby
strongly typed. Consider:

$ ruby -e 'puts 1+1'
-e:1:in `+': String can't be coerced into Fixnum (TypeError)
from -e:1
$ perl -e 'print 1+1,\n;'
2

However, ruby 

Re: [GENERAL] Inheritance vs. LIKE - need advice

[William Bug]

Once again, many many thanks Jeff for taking the time to think  
through these issues and provide your well-informed comments  opinions!


On Aug 10, 2005, at 4:09 PM, Jeff Davis wrote:


William Bug wrote:

As you say, both LIKE  INHERIT are a bit anemic and unnecessary,   
since
what they provide can be implemented via VIEWs and RULEs/  
TRIGGERs.  I'd




I'd like to point out that INHERITS is unique,


a point well taken.  I can remember how excited I was when Oracle  
first started providing object extensions (which provides richer  
capabilities than INHERITs currently does).  Of course, that was a  
good decade after PostgreSQL/post-Ingres had added INHERITs (http:// 
en.wikipedia.org/wiki/PostgreSQL).



although I'm not sure all
of the exact differences. The main difference that I see is that  
SELECT

reads from multiple tables, and to make it only read one you do SELECT
... ONLY.

LIKE is purely syntactic sugar. Use it whenever it saves time and/or
reduces confusion.



VIEWs, at least).  Being able to create  MATERIALIZED VIEWs would
probably help to remove any realtime  performance issues  
introduced by
requiring additional JOINs to  harvest this view of the data.  I  
can do
this myself via RULEs/ TRIGGERs or use the system being developed  
as an

extension to Pg -  The matview Project
(http://gborg.postgresql.org/project/matview/ projdisplay.php).



Agreed. Materialized views are a great way to get whatever performance
benefits you need from physical representation without imposing on the
logical layout.

For some reason a good document on the subject is on a server  
that's not
responding right now (hopefully temporarily!). Here's the google  
cache:
http://66.102.7.104/search? 
q=cache:jaaXngt0hioJ:jonathangardner.net/PostgreSQL/ 
materialized_views/matviews.html+materialized+view+postgresqlhl=en


Many thanks for this link.  I had grown quite addicted to  
Materialized Views in Oracle, especially when working on OLAP  
applications.  Though I've been a very happy convert to PostgreSQL  
(for about 2 years ago), I've really missed having MATERIALIZED VIEWs  
integrated into the core DDL SQL of the RDBMS.





I actually think the INHERITs  LIKE features of PostgreSQL - in   
their

simplicity - potentially offer a better means to providing  Object
properties to SQL relations, than the more complex, fully  realized
Object-Relational systems, which are inherently better  suited to the
task of providing a seamless persistence mechanism for  OOP code
objects.  I do hope its not true Pg's INHERIT  LIKE  features are
vestigial and will continue to be developed into the  future.  in the



I think I overstated what I meant in my previous email... it should be
more like it's in a stasis rather than on life support. When PG
solves some of the table partitioning issues in future releases,  
you can
bet that those features will help complete the inheritance model.  
After
a while, INHERITS will also be merely a syntax for capabilities  
that are

available otherwise.



meantime, in light of what you say regarding the lack  of active
development and maintenance they are receiving, I'll  probably lay  
off
using them much for now.  As you say, heavy use of  INHERITs given  
the

current support given to this feature is more a  liability than a
convenience at this point.



Not too many people use INHERITS. I think it's fairly independent  
in the
code and probably not too many bugs appear, but if a bug creeps in,  
the
limited testing might not catch it. Maybe a brief scan of the -bugs  
list

might indicate whether inheritance is a source of problems, or benign
syntax that primarily depends on other database features which are
well-tested.


Both are excellent points.  I will certainly check the bug lists  
before getting too committed to using INHERITs, though, as you say,  
hopefully the implementation relies on other components in the system  
getting heavy use (and testing).







I wasn't clear about this in that initial post, but my references to
OOP techniques were just by way of analogy.  I wasn't really  
asking  the
question with a thought toward creating a model to mirror my OOP   
models

- to be simply a persistence mechanism.  There are many  wonderful



It was mainly just a warning that sometimes application algorithms  
tend

to mix with the data model.


I'm a VERY STRONG believer in keeping application requirements out of  
the logical data model - probably too much so sometimes.  In general,  
it has rarely served me wrong, when I've needed to go in and write a  
wholly separate application to the same underlying data.  I really  
appreciate your placing INHERITs in this context.  It would have  
completely slipped by me, though it should have been obvious.






So - the real point I was trying to make is:
1) INHERIT  OOP inheritance: each provide a relatively   
efficient

means to model real-world objects with inherent parent- child,
hierarchical relations to one