[SQL] DDL problems: Referential issue?
PostgreSQL 8.3.8 on Gentoo Linux. I've got a junction table: CREATE TABLE participants ( person_fk INTEGER REFERENCES persons (person_id), event_fk INTEGER REFERENCES events (event_id) ON DELETE CASCADE, sort_order INTEGER NOT NULL DEFAULT 1, is_principal BOOLEAN NOT NULL DEFAULT TRUE, PRIMARY KEY (person_fk, event_fk) ); CREATE INDEX event_key ON participants (event_fk); CREATE INDEX person_key ON participants (person_fk); Now I want to add some text to a few participants, but as this will probably only be for a few per cent, I try to create an extra table like this: pgslekt=> CREATE TABLE participant_notes ( pgslekt(> person_fk INTEGER NOT NULL REFERENCES participants (person_fk), pgslekt(> event_fkINTEGER NOT NULL REFERENCES participants (event_fk) ON DELETE CASCADE, pgslekt(> part_note TEXT, pgslekt(> PRIMARY KEY (person_fk, event_fk) pgslekt(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "participant_notes_pkey" for table "participant_notes" ERROR: there is no unique constraint matching given keys for referenced table "participants" I fail to see what is the problem. I even tried to add a unique constraint to participants: pgslekt=> alter table participants add constraint unique_person_event unique (person_fk, event_fk); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "unique_person_event" for table "participants" ALTER TABLE But I still get the same error message as above. This works fine, naturally: pgslekt=> CREATE TABLE participant_notes ( pgslekt(> person_fk INTEGER NOT NULL references persons (person_id), pgslekt(> event_fkINTEGER NOT NULL references events (event_id) ON DELETE CASCADE, pgslekt(> part_note TEXT, pgslekt(> PRIMARY KEY (person_fk, event_fk) pgslekt(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "participant_notes_pkey" for table "participant_notes" CREATE TABLE However, I think that this table should reference participants, not the primary tables persons and events. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wed, Nov 4, 2009 at 11:03 AM, Leif Biberg Kristensen wrote: > PostgreSQL 8.3.8 on Gentoo Linux. > > I've got a junction table: > > CREATE TABLE participants ( > person_fk INTEGER REFERENCES persons (person_id), > event_fk INTEGER REFERENCES events (event_id) ON DELETE CASCADE, > sort_order INTEGER NOT NULL DEFAULT 1, > is_principal BOOLEAN NOT NULL DEFAULT TRUE, > PRIMARY KEY (person_fk, event_fk) > ); > CREATE INDEX event_key ON participants (event_fk); > CREATE INDEX person_key ON participants (person_fk); > > Now I want to add some text to a few participants, but as this will probably > only be for a few per cent, I try to create an extra table like this: > > pgslekt=> CREATE TABLE participant_notes ( > pgslekt(> person_fk INTEGER NOT NULL REFERENCES participants > (person_fk), > pgslekt(> event_fk INTEGER NOT NULL REFERENCES participants (event_fk) > ON DELETE CASCADE, > pgslekt(> part_note TEXT, > pgslekt(> PRIMARY KEY (person_fk, event_fk) > pgslekt(> ); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "participant_notes_pkey" for table "participant_notes" > ERROR: there is no unique constraint matching given keys for referenced table > "participants" > > I fail to see what is the problem. I even tried to add a unique constraint to > participants: You're referencing a single column, which does not have a unique key on it. Being part of a two column unique PK index doesn't count, as you could have an entry where one column or the other repeats on its own while the other column changes. You might want the syntax: FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) where you FK a pair of columns to a pair of other columns. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wednesday 4. November 2009 19.24.29 Scott Marlowe wrote: > You're referencing a single column, which does not have a unique key > on it. Being part of a two column unique PK index doesn't count, as > you could have an entry where one column or the other repeats on its > own while the other column changes. You might want the syntax: > > FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn > [, ... ] ) > > where you FK a pair of columns to a pair of other columns. Thank you very much! pgslekt=> CREATE TABLE participant_notes ( pgslekt(> person_fk INTEGER, pgslekt(> event_fkINTEGER, pgslekt(> part_note TEXT, pgslekt(> FOREIGN KEY (person_fk, event_fk) REFERENCES participants (person_fk, event_fk) pgslekt(> ); CREATE TABLE I'd missed that particular syntax. This table is now without a primary key, but is that a problem? I don't expect it to grow beyond maybe a few thousand rows. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen wrote: > On Wednesday 4. November 2009 19.24.29 Scott Marlowe wrote: >> You're referencing a single column, which does not have a unique key >> on it. Being part of a two column unique PK index doesn't count, as >> you could have an entry where one column or the other repeats on its >> own while the other column changes. You might want the syntax: >> >> FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn >> [, ... ] ) >> >> where you FK a pair of columns to a pair of other columns. > > Thank you very much! > > pgslekt=> CREATE TABLE participant_notes ( > pgslekt(> person_fk INTEGER, > pgslekt(> event_fk INTEGER, > pgslekt(> part_note TEXT, > pgslekt(> FOREIGN KEY (person_fk, event_fk) REFERENCES participants > (person_fk, event_fk) > pgslekt(> ); > CREATE TABLE > > I'd missed that particular syntax. > > This table is now without a primary key, but is that a problem? I don't expect > it to grow beyond maybe a few thousand rows. Hard to say, but if you really need a PK, you can always create one later. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wednesday 4. November 2009 19.37.41 Scott Marlowe wrote: > On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen > wrote: >> I'd missed that particular syntax. >> >> This table is now without a primary key, but is that a problem? I don't >> expect it to grow beyond maybe a few thousand rows. > > Hard to say, but if you really need a PK, you can always create one later. This looks strange to me, but it works: pgslekt=> CREATE TABLE participant_notes ( pgslekt(> person_fk INTEGER NOT NULL, pgslekt(> event_fkINTEGER NOT NULL, pgslekt(> part_note TEXT, pgslekt(> PRIMARY KEY (person_fk, event_fk), pgslekt(> FOREIGN KEY (person_fk, event_fk) REFERENCES participants (person_fk, event_fk) pgslekt(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "participant_notes_pkey" for table "participant_notes" CREATE TABLE And is probably what I want. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen wrote: > On Wednesday 4. November 2009 19.37.41 Scott Marlowe wrote: >> On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen >> wrote: >>> I'd missed that particular syntax. >>> >>> This table is now without a primary key, but is that a problem? I don't >>> expect it to grow beyond maybe a few thousand rows. >> >> Hard to say, but if you really need a PK, you can always create one later. > > This looks strange to me, but it works: > > pgslekt=> CREATE TABLE participant_notes ( > pgslekt(> person_fk INTEGER NOT NULL, > pgslekt(> event_fk INTEGER NOT NULL, > pgslekt(> part_note TEXT, > pgslekt(> PRIMARY KEY (person_fk, event_fk), > pgslekt(> FOREIGN KEY (person_fk, event_fk) REFERENCES participants > (person_fk, event_fk) > pgslekt(> ); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "participant_notes_pkey" for table "participant_notes" > CREATE TABLE Note that this will limit you to one record in your participant notes for each record in the participants table. If you need > 1 of those, then you could either create a serial and use that for a PK, or PK on person_fk, event_fk and part_not, assuming part_note doesn't get real big. If it does you can PK on something like event, person, and md5(part_note) or something along those lines. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wednesday 4. November 2009 21.03.26 Scott Marlowe wrote: > On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen > > This looks strange to me, but it works: > > > > pgslekt=> CREATE TABLE participant_notes ( > > pgslekt(> person_fk INTEGER NOT NULL, > > pgslekt(> event_fkINTEGER NOT NULL, > > pgslekt(> part_note TEXT, > > pgslekt(> PRIMARY KEY (person_fk, event_fk), > > pgslekt(> FOREIGN KEY (person_fk, event_fk) REFERENCES participants > > (person_fk, event_fk) > > pgslekt(> ); > > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > > "participant_notes_pkey" for table "participant_notes" > > CREATE TABLE > > Note that this will limit you to one record in your participant notes > for each record in the participants table. That's exactly what I want :) For all practical purposes, the design is equivalent to adding a TEXT column to the participants table. But as I expect a very small number of notes compared to the number of rows in the participants table, I prefer to create a small extra table rather than having a large number of null values in the participants table. Performance-wise, it probably doesn't matter much. It's more a matter of taste. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wed, Nov 4, 2009 at 1:14 PM, Leif Biberg Kristensen wrote: > On Wednesday 4. November 2009 21.03.26 Scott Marlowe wrote: >> On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen >> > This looks strange to me, but it works: >> > >> > pgslekt=> CREATE TABLE participant_notes ( >> > pgslekt(> person_fk INTEGER NOT NULL, >> > pgslekt(> event_fk INTEGER NOT NULL, >> > pgslekt(> part_note TEXT, >> > pgslekt(> PRIMARY KEY (person_fk, event_fk), >> > pgslekt(> FOREIGN KEY (person_fk, event_fk) REFERENCES participants >> > (person_fk, event_fk) >> > pgslekt(> ); >> > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index >> > "participant_notes_pkey" for table "participant_notes" >> > CREATE TABLE >> >> Note that this will limit you to one record in your participant notes >> for each record in the participants table. > > That's exactly what I want :) > > For all practical purposes, the design is equivalent to adding a TEXT column > to the participants table. But as I expect a very small number of notes > compared to the number of rows in the participants table, I prefer to create a > small extra table rather than having a large number of null values in the > participants table. Performance-wise, it probably doesn't matter much. It's > more a matter of taste. Exactly. Note that null values i pgsql take up VERY little space, so performance-wise one table is likely faster, but it's not gonna break the bank to have two. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Mangled high bit characters with pg_server_prepare=0 in DBD::Pg
I've just tracked down a serious bug with PG_BYTEA columns, which is probably
perl specific. But I thought
people might want to know:
Package: libdbd-pg-perl
Version: upgrading to 2.8.7 compared to 1.49. Our 1.49 was patched for the
memory leak in BYTEA.
With 2.8.7, if pg_server_prepare is set to zero, you can no longer store
characters above
127 to the database. pg_server_prepare has to be set to zero because the
notoriously unstable
DBD:Pg distribution changed how bind parameters work, and pg_server_prepare=0
is the only way
to get back the original behavior.
The behavior is true if you declare it at connect time, or at statement handle
time.
#!/usr/local/bin/perl -w
#
# Test script for character mangling bug in DBD:Pg
# Using this table:
# CREATE TABLE test (key serial,val1 text,val2 bytea);
#
use DBI;
use DBD::Pg;
printf("Testing %-20s: %s\n", DBD::Pg, DBD::Pg->VERSION );
# Prep string with values from 0 to 255
my $string;
for(my $i=0; $i<256; $i++) {
$data .= chr($i);
}
my($dbh) = DBI->connect("dbi:Pg:dbname=","XX","");
#$dbh->do("SET CLIENT_ENCODING TO 'LATIN1'") or die;
#$dbh->{pg_expand_array}++;
# Prepare
my $context_key = 1;
$query_str = "UPDATE test SET val2=? WHERE key=?";
$sth = $dbh->prepare($query_str) or die;
# Should work, does work
$sth->bind_param(1, $data, {pg_type => DBD::Pg::PG_BYTEA});
$sth->bind_param(2, $context_key++);
$sth->execute() or die "Database Error: $DBI::err ... $DBI::errstr; query_str:
$query_str";
# Should work, mangles characters above 128
$sth->{pg_server_prepare} = 0;
$sth->bind_param(1, $data, {pg_type => DBD::Pg::PG_BYTEA});
$sth->bind_param(2, $context_key++);
$sth->execute() or die "Database Error: $DBI::err ... $DBI::errstr; query_str:
$query_str";
# Should fail due to mismatched primary key, but does not
$sth->bind_param(1, $data, {pg_type => DBD::Pg::PG_BYTEA});
$sth->bind_param(2, 999);
$sth->execute() or die "Database Error: $DBI::err ... $DBI::errstr; query_str:
$query_str";
Here is a dump after a run. Note all \377 is returned for all high characters:
key | val1 | val2
1 | |
\000\001\002\003\004\005\006\007\010\011\012\013\014\015\016\017\020\021\022\023\024\025\026\027\030\031\032\033\034\035\036\037
!"#$%&'()*+,-./0123456789:;<=>?...@abcdefghijklmnopqrstuvwxyz[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~\177\200\201\202\203\204\205\206\207\210\211\212\213\214\215\216\217\220\221\222\223\224\225\226\227\230\231\232\233\234\235\236\237\240\241\242\243\244\245\246\247\250\251\252\253\254\255\256\257\260\261\262\263\264\265\266\267\270\271\272\273\274\275\276\277\300\301\302\303\304\305\306\307\310\311\312\313\314\315\316\317\320\321\322\323\324\325\326\327\330\331\332\333\334\335\336\337\340\341\342\343\344\345\346\347\350\351\352\353\354\355\356\357\360\361\362\363\364\365\366\367\370\371\372\373\374\375\376\377
2 | |
\000\001\002\003\004\005\006\007\010\011\012\013\014\015\016\017\020\021\022\023\024\025\026\027\030\031\032\033\034\035\036\037
!"#$%&'()*+,-./0123456789:;<=>?...@abcdefghijklmnopqrstuvwxyz[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~\177\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377\377
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Learning about WITH RECURSIVE
Can anyone one explain why a "WITH RECURSIVE" query has the same results regardless whether UNION or UNION ALL is specified? broersr=> WITH RECURSIVE t(n) AS ( broersr(> VALUES (1) broersr(> UNION broersr(> SELECT n+1 FROM t WHERE n < 100 broersr(> ) broersr-> SELECT sum(n) FROM t; sum -- 5050 (1 row) broersr=> WITH RECURSIVE t(n) AS ( broersr(> VALUES (1) broersr(> UNION ALL broersr(> SELECT n+1 FROM t WHERE n < 100 broersr(> ) broersr-> SELECT sum(n) FROM t; sum -- 5050 (1 row) -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Learning about WITH RECURSIVE
Richard Broersma writes: > Can anyone one explain why a "WITH RECURSIVE" query has the same > results regardless whether UNION or UNION ALL is specified? Well, if the rows are all different anyway, UNION isn't going to eliminate any ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Learning about WITH RECURSIVE
On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane wrote: > Richard Broersma writes: >> Can anyone one explain why a "WITH RECURSIVE" query has the same >> results regardless whether UNION or UNION ALL is specified? > > Well, if the rows are all different anyway, UNION isn't going to > eliminate any ... Okay thanks. I see that I need to spend more time with the on-line docs. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Learning about WITH RECURSIVE
On Wed, Nov 4, 2009 at 2:11 PM, Tom Lane wrote: > Richard Broersma writes: >> Can anyone one explain why a "WITH RECURSIVE" query has the same >> results regardless whether UNION or UNION ALL is specified? > > Well, if the rows are all different anyway, UNION isn't going to > eliminate any ... Actually I'm still confused. I must me missing something. When I manually following the directions of: http://www.postgresql.org/docs/8.4/interactive/queries-with.html I get the following when I try: WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t; (1) --initial non-recursive working table (1) UA (2) = (1,2) --new(1) working table (1,2) UA (2,3) = (1,2,2,3) --new(2) working table (1,2,2,3) UA (2,3,3,4) = (1,2,2,2,3,3,3,4) --new(3) working table -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Learning about WITH RECURSIVE
Richard Broersma writes: > Actually I'm still confused. I must me missing something. When I > manually following the directions of: > http://www.postgresql.org/docs/8.4/interactive/queries-with.html > I get the following when I try: > WITH RECURSIVE t(n) AS ( > VALUES (1) > UNION ALL > SELECT n+1 FROM t WHERE n < 100 > ) > SELECT sum(n) FROM t; > (1) --initial non-recursive working table > (1) UA (2) = (1,2) --new(1) working table > (1,2) UA (2,3) = (1,2,2,3) --new(2) working table > (1,2,2,3) UA (2,3,3,4) = (1,2,2,2,3,3,3,4) --new(3) working table You're confusing the working table with the final output. In this test case, the working table contains exactly one row after each step (except after the last, when it contains no rows). That one row is also added to the result, but we don't use the whole result for the next iteration of the recursive term. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
