Re: [SQL] Referential integrity (foreign keys) across multiple tables
On Sun, Jul 23, 2006 at 01:32:37PM -0500, Bruno Wolff III wrote: > On Sat, Jul 22, 2006 at 14:32:57 +0100, > Richard Jones <[EMAIL PROTECTED]> wrote: > > > > Now I want to add a column to page_contents, say called link_name, > > which is going to reference the pages.url column for the particular > > host that this page belongs to. > > What are you trying to accomplish by this? Data integrity. > The information is available by doing a join. If you are trying to > simplify things for applications, you can probably do it with a view > or rules depending on whether you want to have an updatable view. If > you are denormalizing for performance and want constraints to > maintain consistancy, then you probably want to push the hostid down > to page_contents as well as the url. These could both be set with a > trigger. (I think a rule could be used as well.) So if I get this right, I should use a trigger to ensure that the old code causes the hostid field to be set in page_contents? Rich. -- Richard Jones, CTO Merjis Ltd. Merjis - web marketing and technology - http://merjis.com Team Notepad - intranets and extranets for business - http://team-notepad.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Rows with exclusive lock
On Sun, 23 Jul 2006, Alvaro Herrera wrote: Martin Marques escribió: After the SELECT FOR UPDATE other transactions can still see the locked rows. I want a read/write lock, so no one can access does rows. SELECT FOR UPDATE acquires an exclusive lock, but other transactions must try to acquire a lock on the rows as well, or they won't be locked. You can try using SELECT FOR SHARE (new as of 8.1) if you want some transactions to hold shared (read) locks. Sorry for not getting it clear the first time. What I want is something like "LOCK table IN ACCESS EXCLUSIVE MODE", but at row level. IOW, SELECT FOR UPDATE blocks other SELECTs FOR UPDATE and SELECTs FOR SHARE, but it does not block plain SELECT. So, this is not posible. :-( -- 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 - Lic. Martín Marqués | SELECT 'mmarques' || Centro de Telemática| '@' || 'unl.edu.ar'; Universidad Nacional| DBA, Programador, del Litoral | Administrador - ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Primary key constraint violation without error?
Actually it's not violation, but I get no error message on violating
it...
The story is like this. I have few tables from where I extract
messageIds for particular users. Then, last 100 messages for each user I
transfer to spare table, to do something with that. That spare table has
strange behaviour.
I have two functions. First one extract last 100 messageIds for
particular user, and second one finds all the users, and then inserts
last 100 messageIds for particular user.
The table definition is like this:
CREATE TABLE mes_del
(
"messageId" int4 NOT NULL,
CONSTRAINT pk PRIMARY KEY ("messageId")
)
WITHOUT OIDS;
And the two functions are like this:
-- this function returns last 100 messageIds for particular user
CREATE OR REPLACE FUNCTION punibrisitablica(int4)
RETURNS SETOF mes_del AS
$BODY$SELECT
messages.id as messagesId
FROM
users
JOIN phone_numbers
ON users.id = phone_numbers.user_id
JOIN messages
ON messages.phone_number = phone_numbers.phone_number
where
users.id = $1
order by
messages.time desc
limit 100;$BODY$
LANGUAGE 'sql' VOLATILE;
-- this function goes trough all the users and inserts messageIds
-- to table mes_del
CREATE OR REPLACE FUNCTION punimessages()
RETURNS bool AS
$BODY$
declare
userId users%ROWTYPE;
begin
truncate table mes_del;
FOR userId IN
SELECT users.id FROM users ORDER BY users.id DESC limit 5
LOOP
INSERT INTO mes_del SELECT * FROM puniBrisiTablica(userId.id);
RAISE NOTICE 'Ubacili smo za usera %.', userId.id;
END LOOP;
return true;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
I appologize for the line breaks, but Evolution is a bit stupid email
client...
In second function there is LIMIT 5, because there are 4 users in my
database, and it's going to be easier to explain like this.
Now, there is sequence of the commands I run trough psql:
First, I truncate the table mes_del:
l_netsms=# truncate table mes_del;
TRUNCATE TABLE
Then I run function punimessages() for filling the messageIds to the
mes_del table:
l_netsms=# select punimessages();
NOTICE: Ubacili smo za usera 4162.
NOTICE: Ubacili smo za usera 4161.
NOTICE: Ubacili smo za usera 4160.
NOTICE: Ubacili smo za usera 4159.
NOTICE: Ubacili smo za usera 4158.
punimessages
--
t
(1 row)
l_netsms=# select count(*) from mes_del;
count
---
60
(1 row)
There are 60 messages for those five users.
Now I run the function again:
l_netsms=# select punimessages();
NOTICE: Ubacili smo za usera 4162.
NOTICE: Ubacili smo za usera 4161.
NOTICE: Ubacili smo za usera 4160.
NOTICE: Ubacili smo za usera 4159.
NOTICE: Ubacili smo za usera 4158.
punimessages
--
t
(1 row)
Shouldn't I get errors that I'm violating primary key constraint when
INSERTing again same data?
l_netsms=# select count(*) from mes_del;
count
---
60
(1 row)
l_netsms=#
If I execute INSERT statement from the second function, I get the error:
l_netsms=# INSERT INTO mes_del SELECT * FROM puniBrisiTablica(4158);
ERROR: duplicate key violates unique constraint "pk"
l_netsms=#
This is expected, I'm just unsure why ain't I receiving that error when
running punimessages() function?
Postgres is 8.1.2 running on linux 2.6.17.
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(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: [SQL] Primary key constraint violation without error?
Mario Splivalo wrote: Actually it's not violation, but I get no error message on violating it... CREATE OR REPLACE FUNCTION punimessages() RETURNS bool AS $BODY$ declare userId users%ROWTYPE; begin truncate table mes_del; ^^^ This at the start of your inserts is why you're not seeing an error :-) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Primary key constraint violation without error?
On Mon, Jul 24, 2006 at 02:12:39PM +0200, Mario Splivalo wrote: > Now I run the function again: > > l_netsms=# select punimessages(); > NOTICE: Ubacili smo za usera 4162. > NOTICE: Ubacili smo za usera 4161. > NOTICE: Ubacili smo za usera 4160. > NOTICE: Ubacili smo za usera 4159. > NOTICE: Ubacili smo za usera 4158. > punimessages > -- > t > (1 row) > > Shouldn't I get errors that I'm violating primary key constraint when > INSERTing again same data? No, because punimessages() has a truncate statement that empties the table each time the function is called. Should that truncate statement be there? -- Michael Fuhr ---(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: [SQL] System catalog table privileges
On 7/21/06, Hilary Forbes <[EMAIL PROTECTED]> wrote: Aaron Thanks for this one - I had actually wondered about doing that but the trouble is that they say that they need up to the minute reports not "as of last night". Indeed, I do have another app where I do just that because I find that reports indexes/requirements are very different to transactional type requirements. However, you have made me make up my mind to see if I can persuade them to work on data that is a day old. I have heard "I need up to the minute data" a lot but have NEVER seen it to be true. I guess if you are trading stocks on the stock market and need to buy and sell immediately as the prices change then you would have a reason but almost always business users think they need things now when they don't. You could also look at the cost/benefit: if they bring the database down, how much would it cost the business? If they are working on day old data, how much would it cost? Get the user to write down and justify their numbers. This will show to you and the user whether it is really necessary to report off of the live data. Good luck!== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ==
Re: [SQL] Referential integrity (foreign keys) across multiple tables
On 7/22/06, Richard Jones <[EMAIL PROTECTED]> wrote: Simplified schema: create table hosts (id serial primary key,hostname text not null ); create table pages (id serial primary key,hostid int not null references hosts (id), url text not null,unique (hostid, url) ); create table page_contents (pageid int not null references pages (id),section text not null );(There are many hosts, many pages per host, and many page_contents sections per page).Now I want to add a column to page_contents, say called link_name,which is going to reference the pages.url column for the particularhost that this page belongs to.Something like: alter table page_contents add link_name text; alter table page_contentsadd constraint foo foreign key (p.hostid, link_name)references pages (hostid, url)where p.id = pageid; The easiest, and not necessarily elegant, way to acomplish this is to create linkid rather than link_name and make it a foreign key to pages.id. Then add a trigger that checks to make sure the pages you link to from page_contents to pages is for the same host. If not, raise an exception. Another option is to do this: create table page_contents ( hostid int not null, url text not null, linkhostid int, linkurl text, section text not null, foreign key (hostid, url) references pages (hostid, url), foreign key (linkhostid, linkurl) references pages (hostid, url) );Or if you really want to restructure things: create table hosts ( id serial primary key, hostname text not null ); create table pages ( id serial primary key, url text not null, unique (url) ); create table page_contents ( pageid int not null references pages (id), hostsid int not null references hosts (id), linkpageid int references pages(id), section text not null );That should give you some options to play with.As a side comment, you should also name your primary key columns more meaningfully. Use hosts.hostsid and pages.pagesid, not hosts.id and pages.id. When you begin writing large queries, the use of the column name id all over the place will make your queries more prone to error, harder to read and harder to write. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Referential integrity (foreign keys) across multiple tables
On Mon, Jul 24, 2006 at 09:59:07 +0100, Richard Jones <[EMAIL PROTECTED]> wrote: > On Sun, Jul 23, 2006 at 01:32:37PM -0500, Bruno Wolff III wrote: > > On Sat, Jul 22, 2006 at 14:32:57 +0100, > > Richard Jones <[EMAIL PROTECTED]> wrote: > > > > > > Now I want to add a column to page_contents, say called link_name, > > > which is going to reference the pages.url column for the particular > > > host that this page belongs to. > > > > What are you trying to accomplish by this? > > Data integrity. This doesn't make sense in isolation. If that is all you are trying to do, then you don't need to do anything to the database design as the information is already there. The application just needs to do a join when querying the data. > > The information is available by doing a join. If you are trying to > > simplify things for applications, you can probably do it with a view > > or rules depending on whether you want to have an updatable view. If > > you are denormalizing for performance and want constraints to > > maintain consistancy, then you probably want to push the hostid down > > to page_contents as well as the url. These could both be set with a > > trigger. (I think a rule could be used as well.) > > So if I get this right, I should use a trigger to ensure that the old > code causes the hostid field to be set in page_contents? No unless you are trying to do something else in addition to maintaining data integrity. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Referential integrity (foreign keys) across multiple tables
On Mon, Jul 24, 2006 at 12:51:48PM -0500, Bruno Wolff III wrote: > On Mon, Jul 24, 2006 at 09:59:07 +0100, > Richard Jones <[EMAIL PROTECTED]> wrote: > > On Sun, Jul 23, 2006 at 01:32:37PM -0500, Bruno Wolff III wrote: > > > On Sat, Jul 22, 2006 at 14:32:57 +0100, > > > Richard Jones <[EMAIL PROTECTED]> wrote: > > > > > > > > Now I want to add a column to page_contents, say called link_name, > > > > which is going to reference the pages.url column for the particular > > > > host that this page belongs to. > > > > > > What are you trying to accomplish by this? > > > > Data integrity. > > This doesn't make sense in isolation. If that is all you are trying to do, > then you don't need to do anything to the database design as the information > is already there. The application just needs to do a join when querying the > data. I'm not sure what this means. By "data integrity" I just meant that I don't want applications to create page_contents.link_name fields which could point to non-existent URLs. (A URL consists of a particular hostid and url, since you're not allowed to have one host pointing to pages on another, and this is where the requirement for a foreign key which spans two tables comes from). Perhaps I meant "data consistency"? Anyway without some sort of check, be it a reference or a trigger -- assuming a trigger is possible -- then an application might create such a bad link. Rich. -- Richard Jones, CTO Merjis Ltd. Merjis - web marketing and technology - http://merjis.com Team Notepad - intranets and extranets for business - http://team-notepad.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Referential integrity (foreign keys) across multiple tables
On Mon, Jul 24, 2006 at 12:26:15PM -0500, Aaron Bono wrote: > On 7/22/06, Richard Jones <[EMAIL PROTECTED]> wrote: > > create table hosts ( > >id serial primary key, > >hostname text not null > > ); > > > > create table pages ( > >id serial primary key, > >hostid int not null references hosts (id), > >url text not null, > >unique (hostid, url) > > ); > > > > create table page_contents ( > >pageid int not null references pages (id), > >section text not null > > ); > > > >(There are many hosts, many pages per host, and many page_contents > >sections per page). > > > >Now I want to add a column to page_contents, say called link_name, > >which is going to reference the pages.url column for the particular > >host that this page belongs to. > > > >Something like: > > > > alter table page_contents add link_name text; > > alter table page_contents > >add constraint foo foreign key (p.hostid, link_name) > >references pages (hostid, url) > >where p.id = pageid; > > The easiest, and not necessarily elegant, way to acomplish this is to create > linkid rather than link_name and make it a foreign key to pages.id. Unfortunately this isn't possible :-( My schema above is simplified a little too far. In the real schema we keep old versions of pages around in the pages table (we move the 'url' field to a 'url_deleted' field so that the unique (hostid, url) isn't violated by the new version of the page). This means that the pageid can be updated, so link_name must store a url, not a pageid. > Then add a trigger that checks to make sure the pages you link to > from page_contents to pages is for the same host. If not, raise an > exception. I think though that this suggestion is right. I'm not sure what difference it makes if it's link_name or linkid, but it looks like I'll have to write a trigger for this. It doesn't seem like there's a way using just ordinary foreign keys. Rich. -- Richard Jones, CTO Merjis Ltd. Merjis - web marketing and technology - http://merjis.com Team Notepad - intranets and extranets for business - http://team-notepad.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Referential integrity (foreign keys) across multiple tables
Richard Jones wrote: Simplified schema: create table hosts ( id serial primary key, hostname text not null ); create table pages ( id serial primary key, hostid int not null references hosts (id), url text not null, unique (hostid, url) ); create table page_contents ( pageid int not null references pages (id), section text not null ); (There are many hosts, many pages per host, and many page_contents sections per page). Now I want to add a column to page_contents, say called link_name, which is going to reference the pages.url column for the particular host that this page belongs to. Ah! It's only from reading later messages that I realise you're trying to ensure that a link in page_contents can only reference pages on the same host as itself. Something like: alter table page_contents add link_name text; alter table page_contents add constraint foo foreign key (p.hostid, link_name) references pages (hostid, url) where p.id = pageid; Obviously that second statement isn't going to compile. I don't want to add the hostid column to page_contents table because I have a lot of old code accessing the database which would be hard to change (the old code would no longer be able to insert page_contents rows). I'm sure you know, but for the benefit of the list the problem here is that the surrogate primary-key on "pages" has concealed valuable information (i.e. the hostid) from the "page_contents" table. Unfortunate that you can't update the application [* see rant below] Is this possible somehow? Perhaps by adding a second table? Do I have to use triggers, and if so is that as robust as referential integrity? Write triggers to enforce the host dependency. Just make a table of all the columns involved on all three tables and decide what (if anything) should happen when a value is inserted/updated/deleted. Write your triggers then write a short test script. Oh, test it with concurrent transactions too - just to make sure it's doing what you'd expect. The only reason hand-built triggers would be less reliable than built-in foreign-keys is because they'll have had less testing. The other alternative would be to add the hostid column to page_contents and use a BEFORE trigger to set it based on the pageid the application inserts. Then you can have a straightforward foreign-key. If your application can't cope with having a column added to the table you might have to mask this with a view. [*minor rant follows: Why is it that when changes to the database schema break applications because the column-names are hard-wired, it is the RDBMS that is at fault? I'm prepared to bet good money (up to the value of say, a whole pound) that the primary key is just being used anonymously in the application and that if the database interface had been able to refer to it as "column(s) referencing pages primary-key" then Richard wouldn't have a problem to post about. And it's relational databases that aren't "agile" enough for the modern world, not most of your programming languages. ] -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CREATE TABLE AS inside of a function
So, I've changed my code as Erik suggested: CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ DECLARE lv RECORD; BEGIN FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS SELECT ldev FROM ldevrg WHERE rg=' || quote_literal($lv) || ';' END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; And I get: psql:rgio.sql:32: ERROR: syntax error at or near "$" at character 33 QUERY: SELECT 'CREATE TABLE rgio_' || $ $1 || ' AS SELECT ldev FROM ldevrg WHERE rg=' || quote_literal($ $2 ) || ';' END LOOP CONTEXT: SQL statement in PL/PgSQL function "rgio" near line 23 psql:rgio.sql:32: LINE 1: SELECT 'CREATE TABLE rgio_' || $ $1 || ' AS psql:rgio.sql:32: ^ From: [EMAIL PROTECTED] on behalf of Erik Jones Sent: Fri 7/21/2006 3:04 PM To: Rodrigo De Leon Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] CREATE TABLE AS inside of a function Rodrigo De Leon wrote: > On 7/21/06, Kevin Nikiforuk <[EMAIL PROTECTED]> wrote: >> So now that I've got my loops working, on to my next newbie >> question. I've created my function and in it, I want to loop through >> the results of a select and for each value of my loop counter, I want >> to create a new table, but I can't figure out how to use a variable >> in the name of the new table, see below. >> >> CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ >> DECLARE >> lv RECORD; >> >> BEGIN >> FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP >> CREATE TABLE rgio_$lv AS >> SELECT ldev >> FROM ldevrg >> WHERE rg='$lv'; >> END LOOP; >> RETURN 1; >> END; >> $$ LANGUAGE plpgsql; >> >> Thanks, >> Kevin > > See: > http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > Also, I really recommend enough that you read chapters 32. Extending SQL, 33. Triggers, and 36. Pl/pgSQL in their entirety. In fact, to keep up with the linking to them for you: http://www.postgresql.org/docs/8.1/interactive/extend.html http://www.postgresql.org/docs/8.1/interactive/triggers.html http://www.postgresql.org/docs/8.1/interactive/plpgsql.html And, so that I don't feel like I'm becoming one of those rtfm jerks I always complain about: what you need to do is place your CREATE TABLE statement in an EXECUTE directive like so (inside the the FOR body): EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS SELECT ldev FROM ldevrg WHERE rg=\'' || $lv || '\';' -- this line could have also been: WHERE rg=' || quote_literal($lv) || ';' EXECUTE takes a query in a string to execute and you need to use string concatenation to build the string if you're using variables from the function in the query. Pl/pgSQL doesn't have any variable substitution inside of strings (like in double quoted string in PHP) which is why you need to use the concatenation bit. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Referential integrity (foreign keys) across multiple tables
On Mon, Jul 24, 2006 at 18:53:20 +0100, Richard Jones <[EMAIL PROTECTED]> wrote: > > I'm not sure what this means. By "data integrity" I just meant that I > don't want applications to create page_contents.link_name fields which > could point to non-existent URLs. (A URL consists of a particular > hostid and url, since you're not allowed to have one host pointing to > pages on another, and this is where the requirement for a foreign key > which spans two tables comes from). Perhaps I meant "data > consistency"? Anyway without some sort of check, be it a reference or > a trigger -- assuming a trigger is possible -- then an application > might create such a bad link. I think part of the problem might have been over simplification of the problem. In your example there was no reason to create that new column since the information was available by doing a join between two tables and this would prevent a problem with data being out of sync. I see from another message in this thread that there is really more to this example. It may be that you still don't need the new column, but I haven't examined the new example carefully to say one way or the other. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] CREATE TABLE AS inside of a function
On Mon, 24 Jul 2006, Kevin Nikiforuk wrote: > So, I've changed my code as Erik suggested: > > CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ > DECLARE > lv RECORD; > > BEGIN > FOR lv IN SELECT DISTINCT rg > FROM ldevrg > LOOP > > EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS > SELECT ldev > FROM ldevrg > WHERE rg=' || quote_literal($lv) || ';' I think you want something like lv.rg (no special punctuation) rather than $lv in the above. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
