[GENERAL] Problem with REFERENCES on INHERITS

[William Gordon Rutherdale]

Hi.

I have encountered a problem with references when using INHERITS (on
Postgres 9.1/9.2).  Could someone please explain why this occurs.

Consider this example.

CREATE TABLE primate
(
   id SERIAL PRIMARY KEY,
   name TEXT,
   tale TEXT
);


CREATE TABLE chimp
(
human_friend TEXT
) INHERITS(primate);

INSERT INTO chimp(name, tale, human_friend) VALUES
('Cheetah', 'Curly', 'Tarzan');

INSERT INTO primate(name, tale) VALUES
('King Julien', 'Move it');

SELECT * FROM primate;

==
 id |name |  tale
+-+-
  2 | King Julien | Move it
  1 | Cheetah | Curly
(2 rows)

CREATE TABLE banana_stash
(
id SERIAL,
primate_id INTEGER REFERENCES primate(id),
qty INTEGER
);

INSERT INTO banana_stash(primate_id, qty) VALUES
(1, 17);

==
ERROR:  insert or update on table banana_stash violates foreign key
constraint banana_stash_primate_id_fkey
DETAIL:  Key (primate_id)=(1) is not present in table primate.

INSERT INTO banana_stash(primate_id, qty) VALUES
(2, 22);

==
INSERT 0 1

SELECT * FROM banana_stash;

==
 id | primate_id | qty
++-
  2 |  2 |  22
(1 row)

My problem:  could someone please explain the semantics and why this
behaviour makes sense -- or is it a design error or bug?

To sum up the issue:
- I insert into the derived table (chimp) and get id 1
- I insert into the base table (primate) and get id 2
- I have a foreign key constraint in banana_stash to the
  base table p.k. primate(id)
- inserting to banana_stash with reference to id 2 is okay
- inserting to banana_stash with reference 1 gives error
- both ids 1 and 2 in table primate are supposed to be valid

So why does the one case give an error when the other does not?

Also, is there a way to solve this problem (i.e. remove the error)
without simply chopping out the REFERENCES clause from banana_stash?

-Will


-- 
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] Problem with REFERENCES on INHERITS

[David G Johnston]

William Gordon Rutherdale wrote
 I have encountered a problem with references when using INHERITS (on
 Postgres 9.1/9.2).  Could someone please explain why this occurs.
 
 My problem:  could someone please explain the semantics and why this
 behaviour makes sense -- or is it a design error or bug?
 
 To sum up the issue:
 - I insert into the derived table (chimp) and get id 1
 - I insert into the base table (primate) and get id 2
 - I have a foreign key constraint in banana_stash to the
   base table p.k. primate(id)
 - inserting to banana_stash with reference to id 2 is okay
 - inserting to banana_stash with reference 1 gives error
 - both ids 1 and 2 in table primate are supposed to be valid
 
 So why does the one case give an error when the other does not?
 
 Also, is there a way to solve this problem (i.e. remove the error)
 without simply chopping out the REFERENCES clause from banana_stash?

I didn't read your post in depth but I suspect you have not read and
understood the limitations documented in section 5.8.1

http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html

David J.



--
View this message in context: 
http://postgresql.nabble.com/Problem-with-REFERENCES-on-INHERITS-tp5836326p5836347.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] How do I bump a row to the front of sort efficiently

[Sam Saffron]

I have this query:

select * from topics
order by case when id=1 then 0 else 1 end, bumped_at desc
limit 30

It works fine, bumps id 1 to the front of the sort fine but is
terribly inefficient and scans

OTH

select * from topics where id = 1 is super fast

select * from topics order by bumped_at desc limit 30 is super fast

Even this is fast, and logically equiv as id is primary key unique

select * from topic
where id = 1000
union all
select * from (
  select * from topics
  where id  1000
  order by bumped_at desc
  limit 30
) as x
limit 30


However, the contortions on the above query make it very un-ORM
friendly as I would need to define a view for it but would have no
clean way to pass limits and offsets in.

Is there any clean technique to bump up particular rows to the front
of a sort if a certain condition is met without paying a huge
performance hit?


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