[SQL] Referential integrity (foreign keys) across multiple tables
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. 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). 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? 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 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
[SQL] Rows with exclusive lock
Is it posible to get an exclusive (read/write) lock on certain rows? I don't want to block the whole table, only certain rows, but I want it to be a read/write lock. AFAIK SELECT FOR UPDATE doesn't help with this. Do I have to go for another aproche? -- 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 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: [SQL] Rows with exclusive lock
Martin Marques escribió: > Is it posible to get an exclusive (read/write) lock on certain rows? I > don't want to block the whole table, only certain rows, but I want it to > be a read/write lock. That's what SELECT FOR UPDATE does. > AFAIK SELECT FOR UPDATE doesn't help with this. Why? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Rows with exclusive lock
On Sat, 22 Jul 2006, Alvaro Herrera wrote: Martin Marques escribió: Is it posible to get an exclusive (read/write) lock on certain rows? I don't want to block the whole table, only certain rows, but I want it to be a read/write lock. That's what SELECT FOR UPDATE does. Hi Alvaro, 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. AFAIK SELECT FOR UPDATE doesn't help with this. Why? trans1: prueba2=> BEGIN; BEGIN prueba2=> SELECT * FROM personas FOR UPDATE; codigo | nombre | apellido | tipodoc | docnum ++--+-+-- 3 | Martin | Marques | 1 | 23622139 (1 row) Meanwhile, at this moment trans2: prueba2=> BEGIN; BEGIN prueba2=> SELECT * FROM personas; codigo | nombre | apellido | tipodoc | docnum ++--+-+-- 3 | Martin | Marques | 1 | 23622139 (1 row) pg_locks shows the the lock is RowShareLock, so there is no read lock on those rows, which is what I want. -- 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 4: Have you searched our list archives? http://archives.postgresql.org
