[GENERAL] Table Inheritance and foreign key problem.
I make 3 tables with this senario: b (id) extends a (id) and c (id) references a (id) i insert one record to table b (id = 1) and then i try to insert a record in table C (id = 1) but it is not possible, i get error (look at the sql statements above) NOTE if the C (id) references the b (id) the insert in table C is possible. I try this to postgresql 8.1.2 and 8.1.4 above is the exact SQL STATMENTS: DROP TABLE a; DROP TABLE b; DROP TABLE c; CREATE TABLE a (id integer primary key); CREATE TABLE b (id integer primary key) INHERITS (a); CREATE TABLE c (id integer primary key references a(id)); insert into b values (1); SELECT * from b; id 1 (1 row) SELECT * from a; id 1 (1 row) INSERT INTO c VALUES (1); ERROR: insert or update on table "c" violates foreign key constraint "c_id_fkey" DETAIL: Key (id)=(1) is not present in table "a". Thanks Kostas Maistrelis. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Table Inheritance and foreign key problem.
Kostas Maistrelis wrote: > I make 3 tables with this senario: > b (id) extends a (id) > and > c (id) references a (id) > > i insert one record to table b (id = 1) > and then i try to insert a record in table C (id = 1) > but it is not possible, i get error > (look at the sql statements above) This is a known, documented shortcoming of our inheritance implementation. -- 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
Re: [GENERAL] ECPG. Badly stuck
On July 22, 7:46 pm "Jasbinder Bali" <[EMAIL PROTECTED]> wrote: > I have a C program and have some ECPG code in it. > > How do i display the data that i retrieve from the postgres database > using a simple select statment or calling a stored procedure. > > Is it mandatory to use cursor or there's any other way to print the > retrieved data.. There is no copy to stdout if this is what you want. Normally you retrieve the data with SELECT INTO and then you print the variables in the usual way. And yes, if there are several rows in your result set you can iterate over them by means of a cursor. If you're developing a console based program, of course you (sometimes) just write the values to stdout but other programs might not be console based but might use some widget set. Displaying values in those contexts has nothing to do with ecpg. Joachim ---(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] type of b-tree
Hi, could anybody tell me which variation of B-tree PostgreSQL uses, that it can handle more than one column? Some link would be great. Thanks, David Hoksza ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] type of b-tree
David Hoksza <[EMAIL PROTECTED]> writes: > Hi, could anybody tell me which variation of B-tree PostgreSQL uses, that > it can handle more than one column? Plain old b+ tree ... we just treat the keys as compound, ie the "compare" operation compares columns until finding a difference. > Some link would be great. Use the source Luke ... http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/nbtree/ 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
[GENERAL] Error dumping and restoring postgis db
Hi, We have a database with Postgis extensions installed. When we do a full dump of the database (using pg_dump -Ft), somehow the "geometry" type does not get dumped, so the restore fails. If I try to load the geometry type before restoring, then I have to createlang 'plpgsql' because postgis needs it. Then again the dump fails as it tries to createlang plpgsql again. Would appreciate any pointers. This is with Postgresql 7.4.8 and Postgis 1.1.2 Claire -- Claire McLister[EMAIL PROTECTED] 1684 Nightingale Avenue Suite 201 Sunnyvale, CA 94087408-733-2737(fax) http://www.zeemaps.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] type of b-tree
OK, thanks. And I would like have one more question about B-tree index - how many records are usualy stored in one node (what's arity of the tree)? Thanks, David Hoksza 23. července 2006, 20:03:03, napsal jste: TL> David Hoksza <[EMAIL PROTECTED]> writes: >> Hi, could anybody tell me which variation of B-tree PostgreSQL uses, that >> it can handle more than one column? TL> Plain old b+ tree ... we just treat the keys as compound, ie the TL> "compare" operation compares columns until finding a difference. >> Some link would be great. TL> Use the source Luke ... TL> http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/nbtree/ TL> regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Error dumping and restoring postgis db
On Sun, Jul 23, 2006 at 11:22:18AM -0700, Claire McLister wrote: > We have a database with Postgis extensions installed. When we do a > full dump of the database (using pg_dump -Ft), somehow the "geometry" > type does not get dumped, so the restore fails. What are the exact dump and restore commands you're running? Do you get any errors? Have you verified that the geometry type exists with a command like "\dT geometry" in psql or a query like "SELECT * FROM pg_type WHERE typname = 'geometry'"? > If I try to load the geometry type before restoring, then I have to > createlang 'plpgsql' because postgis needs it. Then again the dump > fails as it tries to createlang plpgsql again. > > Would appreciate any pointers. This is with Postgresql 7.4.8 and > Postgis 1.1.2 What platform? I couldn't reproduce this problem with PostgreSQL 7.4.13 and PostGIS 1.1.4CVS on Solaris 9. Here's what I did (PGUSER is "postgres"): createdb test createlang plpgsql test psql -d test -q -f lwpostgis.sql psql -d test -q -f spatial_ref_sys.sql pg_dump -Ft -f test.dump test dropdb test createdb test pg_restore -d test test.dump The above commands successfully created, dropped, and restored a test database with PostGIS. What happens if you run the same test? What types do you see if you run "pg_restore -l" to display the dump's table of contents? Here's what I get: pg_restore -l test.dump | grep TYPE 5; 2652927 TYPE histogram2d postgres 6; 2652931 TYPE spheroid postgres 7; 2652935 TYPE geometry postgres 8; 2652953 TYPE box3d postgres 9; 2652963 TYPE chip postgres 10; 2652967 TYPE box2d postgres 11; 2653112 TYPE geometry_dump postgres and pg_restore test.dump | grep 'CREATE TYPE' CREATE TYPE histogram2d ( CREATE TYPE spheroid ( CREATE TYPE geometry ( CREATE TYPE box3d ( CREATE TYPE chip ( CREATE TYPE box2d ( CREATE TYPE geometry_dump AS ( What output do you get? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Table Inheritance and foreign key problem.
On Sun, 2006-07-23 at 14:59 +0300, Kostas Maistrelis wrote: > I make 3 tables with this senario: > b (id) extends a (id) > and > c (id) references a (id) > > i insert one record to table b (id = 1) > and then i try to insert a record in table C (id = 1) > but it is not possible, i get error > (look at the sql statements above) I was also caught by the observation you now face. The origin of the issue is that inherited tables do not share a common index on the primary key. In your example, PostgreSQL will also let you have a record with id=1 in table a and b simultaneously, which almost certainly violates your design intentions. PostgreSQL's inheritance is better used to facilitate abstraction and administration of table definitions than it is to provide OO-like subclassing of data. It's likely folly (but not impossible) to use inheritance to represent relationships among abstract representations of instances (er, rows). In the one case where I wanted OO-like behavior badly enough, I did the equivalent of creating a "supertable" c and defined inheriting tables c_a with FK c_a(id) that refers to a(id) and table c_b with FK c_b(id) that refers to table b(id). All c rows with keys that refer to a(id) must be in table c_a, and similarly for b. Fortunately, select from c does a union select of c_a and c_b, and thus provides the abstract view of all instances/rows of c. This would surely be a painful route for more general relationships among complex data. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ---(end of broadcast)--- TIP 6: explain analyze is your friend
