[GENERAL] Trigger Procedure Error: NEW used in query that is not in a rule
Hello. I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing. I think that it's working alright except for the next line: EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT new.*'; PostgreSQL keeps telling me: ERROR: NEW used in query that is not in a rule. I think that this NEW problem is because of the scope of the EXECUTE statement (outside the scope of the trigger), so it doesn't recognize the NEW record. Maybe I could fix it concatenating column names and the 'new' values but I want to do my trigger as flexible as possible (I have several tables to audit). Somebody has any suggestion? Thanks a lot, Javier
Re: [GENERAL] timestamp skew during 7.4 - 8.2 upgrade
On Fri, Aug 10, 2007 at 04:59:52PM -0400, Tom Lane wrote: Karsten Hilbert [EMAIL PROTECTED] writes: On Fri, Aug 10, 2007 at 10:11:29AM +0200, Louis-David Mitterrand wrote: So if I understand correctly, a timestamp_tz is ... ... stored as UTC in the backend ... sent to clients shifted by whatever timezone was requested by the client by one of several mechanisms: - set timezone to ... used by the client - select ... at time zone ... used by the client - the server timezone if neither of the above is used The other point to be clear on is that the shifting is done according to whatever timezone rule files the server currently has. Since politicians keep changing daylight-savings rules, the same UTC date/time might be displayed differently after an update of the relevant rule file. (I am located in Paris, GMT+2, using debian unstable) When using date here is the output on the server where the postgresql upgrade (or more likely that's server's subsequent misconfiguration) changed our timestamps: uruk:~# date Sat Aug 11 10:50:46 CEST 2007 uruk:~# date --utc Sat Aug 11 08:50:49 UTC 2007 uruk:~# and: uruk:~# tzconfig Your current time zone is set to Europe/Paris But, I found something fishy that particular server: uruk:~# hwclock Sat 11 Aug 2007 10:47:36 AM CEST -0.630123 seconds uruk:~# hwclock --utc Sat 11 Aug 2007 12:47:39 PM CEST -0.600430 seconds Whereas on my other servers hwclock --utc displays the same time (is that normal?): zenon:~# hwclock Sat 11 Aug 2007 10:50:21 AM CEST -0.015345 seconds zenon:~# hwclock --utc Sat 11 Aug 2007 10:50:24 AM CEST -0.000235 seconds Is postgres using the same time reference as hwclock or date ? Thanks, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Are these two creation commands functionally identical?
On fim, 2007-08-09 at 20:55 +, [EMAIL PROTECTED] wrote: I want to to know if these two are functionally equivalent. Is this: Create table sales ( saleid BigSerial NOT NULL, userid Bigint NOT NULL, parent_saleid Bigint NOT NULL, primary key (saleid) ) Without Oids; Alter table sales add foreign key (userid) references users (userid) on update restrict on delete restrict; Alter table sales add foreign key (parent_saleid) references sales (saleid) on update restrict on delete restrict; this constraint seems a bit strange to me. are you going to special-case the first insert into this table? Is the above functionally identical to: Create table sales ( saleid BigSerial NOT NULL, userid bigint references users(userid), parent_saleid bigint references sales(saleid), primary key (saleid) ) Without Oids; no these 2 are not fuctionally identical, because the second one does not have a NOT NULL constraint on the foreign keys, allowing you to insert: INSERT INTO sales (saleid,userid,parent_saleid) VALUES (100,null,100); gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] why it doesn't work? referential integrity
Hello I found strange postgresql's behave. Can somebody explain it? Regards Pavel Stehule CREATE TABLE users ( id integer NOT NULL, name VARCHAR NOT NULL, PRIMARY KEY (id) ); INSERT INTO users VALUES (1, 'Jozko'); INSERT INTO users VALUES (2, 'Ferko'); INSERT INTO users VALUES (3, 'Samko'); CREATE TABLE tasks ( id integer NOT NULL, owner INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, worker INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, PRIMARY KEY (id) ); INSERT INTO tasks VALUES (1,1,NULL,NULL); INSERT INTO tasks VALUES (2,2,2,NULL); INSERT INTO tasks VALUES (3,3,3,3); DELETE FROM users WHERE id = 1; -- works simple DELETE FROM users WHERE id = 2; -- works ok DELETE FROM users WHERE id = 3; -- doesn't work, why? ERROR: insert or update on table tasks violates foreign key constraint tasks_checked_by_fkey DETAIL: Key (checked_by)=(3) is not present in table users. CONTEXT: SQL statement UPDATE ONLY public.tasks SET worker = NULL WHERE $1 OPERATOR(pg_catalog.=) worker ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] why it doesn't work? referential integrity
Pavel Stehule [EMAIL PROTECTED] writes: checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, CONTEXT: SQL statement UPDATE ONLY public.tasks SET worker = NULL WHERE $1 OPERATOR(pg_catalog.=) worker This says you mistyped the constraint above to refer to tasks(worker) instead of users(id). Did you? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] why it doesn't work? referential integrity
2007/8/11, Gregory Stark [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, CONTEXT: SQL statement UPDATE ONLY public.tasks SET worker = NULL WHERE $1 OPERATOR(pg_catalog.=) worker This says you mistyped the constraint above to refer to tasks(worker) instead of users(id). Did you? -- Im sorry. I don't understand. It's look like wrong evaluation order: 1. delete from users 2. update tab set col = NULL insead 1. update ... -- remove references 2. delete from users ... Pavel Stehule Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] why it doesn't work? referential integrity
Sorry, I reread your original post. My initial reading was wrong. To make this work I think you'll need to set these constraints to be deferred. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] why it doesn't work? referential integrity
2007/8/11, Gregory Stark [EMAIL PROTECTED]: Sorry, I reread your original post. My initial reading was wrong. To make this work I think you'll need to set these constraints to be deferred. -- it works with deferred constraints . It's strange, it works with two columns but dowsn't work with three columns (without deferred c.). Pavel ---(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: [GENERAL] why it doesn't work? referential integrity
Pavel Stehule [EMAIL PROTECTED] writes: 2007/8/11, Gregory Stark [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, CONTEXT: SQL statement UPDATE ONLY public.tasks SET worker = NULL WHERE $1 OPERATOR(pg_catalog.=) worker This says you mistyped the constraint above to refer to tasks(worker) instead of users(id). Did you? -- Im sorry. I don't understand. It's look like wrong evaluation order: 1. delete from users There's no delete from users in evidence here. Check how your constraints are actually defined, it doesn't look like they're defined they way you claimed they are -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] timestamp skew during 7.4 - 8.2 upgrade
Louis-David Mitterrand [EMAIL PROTECTED] writes: But, I found something fishy that particular server: uruk:~# hwclock Sat 11 Aug 2007 10:47:36 AM CEST -0.630123 seconds uruk:~# hwclock --utc Sat 11 Aug 2007 12:47:39 PM CEST -0.600430 seconds If this is PC-type hardware, I'd guess that something is confused about whether the hardware clock is running in UTC or local time. Is postgres using the same time reference as hwclock or date ? I'd expect PG to get the same results as date. I have no idea what hwclock is really doing. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] why it doesn't work? referential integrity
On Sat, 11 Aug 2007, Pavel Stehule wrote: Hello I found strange postgresql's behave. Can somebody explain it? There's a bug since it should work for any number, but we've likely missed something. I'm not sure why 2 references work, as I'd expect it to stop working after 1 with the likely causes, but one of the constraint checks is happening before the row is finished being updated. I don't think it'll help for this case (since it revolved around multiple tables), but could you try the patch from http://archives.postgresql.org/pgsql-bugs/2007-05/msg00177.php to see if it helps this case? ---(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: [GENERAL] why it doesn't work? referential integrity
2007/8/11, Stephan Szabo [EMAIL PROTECTED]: On Sat, 11 Aug 2007, Pavel Stehule wrote: Hello I found strange postgresql's behave. Can somebody explain it? There's a bug since it should work for any number, but we've likely missed something. I'm not sure why 2 references work, as I'd expect it to stop working after 1 with the likely causes, but one of the constraint checks is happening before the row is finished being updated. I don't think it'll help for this case (since it revolved around multiple tables), but could you try the patch from http://archives.postgresql.org/pgsql-bugs/2007-05/msg00177.php to see if it helps this case? This patch doesn't help. I'll report it as bug. Regards Pavel Stehule ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Installing Postgresql 8.2 on Windows Vista
Johan Runnedahl wrote: I would appreciate any constructive help on this. A constructive answer is to grab VMware Server or VirtualBox and a PG LiveCD. That way you are up and running in 15 minutes and can get your work done instead of fighting with an installer / OS combo that is known to be problematic. -- Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SQL question: checking all required items
On 10/08/2007 22:03, Carlos OrtÃz wrote: Select * from people where person_id in ( Select person_ID from Items_for_people group by Person_id Having Count(*) = ( Select count(*) from Items Where is_required = true)) That seems to work fine! I'd only change having count(*) = ... to having count(*) = ... to allow for people having other items in addition to the required ones. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL question: checking all required items
On 10/08/2007 21:42, Scott Marlowe wrote: Show us the query when you're done, I'm sure there are enough folks who'd like to see your solution. Here's what I came up with: select distinct ip.person_id from items_for_people ip where exists ( ( select item_id from items where is_required = true ) except ( select ip2.item_id from items_for_people ip2 inner join items i on (ip2.item_id = i.item_id) where ip2.person_id = ip.person_id and i.is_required = true ) ) This finds all those who don't have all the required items, whatever else they may have. Comments and improvements are welcome! Thanks for the help, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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: [GENERAL] virtual database
Farhan Mughal wrote: Does PostgreSQL support a Virtual Database like Oracle? No, but we have 2 external approaches: http://veil.projects.postgresql.org/curdocs/index.html http://www.kaigai.gr.jp/index.php?sepgsql -- Best regards, Hannes Dorbath ---(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
