Re: [SQL] About how to use "exception when ??? then "
Emi Lu wrote: From the page: "The PL/pgSQL condition name for each error code is the same as the phrase shown in the table, with underscores substituted for spaces. For example, code 22012, DIVISION BY ZERO, has condition name DIVISION_BY_ZERO. Condition names can be written in either upper or lower case. (Note that PL/pgSQL does not recognize warning, as opposed to error, condition names; those are classes 00, 01, and 02.)" That means pl/pgsql will not recognize error codes under classes 00, 01, 02. Correct - they are informational rather than errors. I'm not sure what it would mean to trap "successful completion" for example. Is there a way, I can output error code? exception when ... then when others then raise notice '%, %', SQLSTATE, SQLERRM; But it seems that SQLERRM and SQLSTATE did not work for me. By the way, I am using postgresql 8.0.1. http://www.postgresql.org/docs/8.1/static/release-8-1.html See section E.4.3.9 - they were defined in 8.1 -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: R: R: Re: [SQL] schema inspection
O [EMAIL PROTECTED] έγραψε στις Mar 16, 2006 : > > pg_catalog.pg_constraint is your (only?) friend. > > I have already examintated this table without results. Seem not to be > a "human-readable" table :( Right you will have to join against pg_class, and make it readable. SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1, pg_class c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid; for column(s) names you will have to do extra homework. > > TIA > Roberto Colmegna > > > > > > Tiscali ADSL 4 Mega Flat > Naviga senza limiti con l'unica Adsl a 4 Mega di velocitΓ a soli 19,95 β¬ > al mese! > Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. > http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: R: Re: R: R: Re: [SQL] schema inspection
O [EMAIL PROTECTED] έγραψε στις Mar 17, 2006 : > > > >SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1, > pg_class > >c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid; > > > >for column(s) names you will have to do extra homework. > > Thanks! I have obtained my query! Here is: > > SELECT > (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS > fromTbl, > (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS > toTbl, > (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid > AND conkey[1]=attnum) AS viaCol > FROM pg_catalog.pg_constraint AS rel WHERE contype='f'; Well thats it if you use only *single column* Foreign keys. In the general case the above will need extra work. Of course you will also have to ensure that the constraint is indeed a FK constraint, that the column is not droped, etc which leads to the answer that enabling statement logging, and then \d and watching the log is a very good friend too. > > TIA > Roberto Colmegna > > > > > Tiscali ADSL 4 Mega Flat > Naviga senza limiti con l'unica Adsl a 4 Mega di velocitΓ a soli 19,95 β¬ > al mese! > Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. > http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 > -- -Achilleus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Checking if date is inside date range
> I would like to check if date is inside a given date range. stupid question :) sorry for bothering you. Jure > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Checking if date is inside date range
Hy list, I would like to check if date is inside a given date range. For instance if date range is datestart column: 10/10/2005 dateend column: 10/20/2005 I would like to return columns for date = 10/15/2005 and not if i enter let's say date = 10/21/2005 thanks for your help, Jure Kodzoman ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] dump with lo
Hi, Marciej, Maciej Piekielniak wrote: > TL> Use 8.1's pg_dump and pg_dumpall to dump from the old server. We fixed > TL> that problem finally. > > I try to copy pg_wrapper,PgCommon.pm to /usr/bin but when i type > pg_dump -V i got: > Argument "." isn't numeric in numeric lt(<) at PgCommon.pm line 439 > Error: You must install at least one > postgresql-client-package pg_wrapper etc. are debian specific and somewhat fragile if you don't strictly obey the debian multi-cluster concept. Using a 8.1 client against a 7.4 server is one of those problematic cases. Install the postgresql-client-8.1 debian package and use /usr/lib/postgresql/8.1/bin/psql directly. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] in PlPgSQL function, how to use variable in a "select ... into .. where " query
Hello,
In pl/pgsql (postgresql 8.01), how to use variables in select .. into ..
command
CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
var1 ALIAS FOR $1;
cm_tableName tableA.col1%TYPE;
T1 VARCHAR := 'sourceTable';
query_value VARCHAR ;
BEGIN
SELECT col2 INTO cm_tableName FROM T1 WHERE col1 = var1 ;
EXECUTE query_value;
RETURN cm_tableName;
END;
$$ language 'plpgsql' IMMUTABLE STRICT;
select test('abc');
Failed.
Also, tried "SELECT col2 INTO cm_tableName FROM || T1 WHERE col1 = ||
var1 " and
"SELECT col2 INTO cm_tableName FROM || T1 || WHERE col1 = || var1"
Failed as well.
T1 and var1 both are variables, may I how to use variables in a "select
... into " query please?
Thanks a lot,
Ying
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] in PlPgSQL function, how to use variable in a "select ...
Does not work either, the whole function is:
create table t1(col1 varchar(3), col2 varchar(100));
insert into t1 values('001', 'Result 1');
insert into t1 values('002', 'Result 2');
insert into t1 values('003', 'Result 3');
CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
col1_valueALIAS FOR $1;
cm_tableName st1_legend.code_map_tablename%TYPE;
lengendTableNameVARCHAR := 't1';
query_valueVARCHAR ;
BEGIN
SELECT INTO cm_tableName col2 FROM lengendTableName WHERE col1 =
col1_value ;
EXECUTE query_value;
RETURN cm_tableName;
END;
$$ language 'plpgsql' IMMUTABLE STRICT;
select test('001');
Error:
ERROR: syntax error at or near "$1" at character 20
QUERY: SELECT col2 FROM $1 WHERE col1 = $2
CONTEXT: PL/pgSQL function "test" line 8 at select into variables
LINE 1: SELECT col2 FROM $1 WHERE col1 = $2
Does it mean I have to use the cursor ?
Thanks,
Ying
I think it is SELECT INTO cm_tableName col2 FROM ... WHERE ...
2006/3/17, Emi Lu <[EMAIL PROTECTED]>:
Hello,
In pl/pgsql (postgresql 8.01), how to use variables in select .. into ..
command
CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
var1 ALIAS FOR $1;
cm_tableName tableA.col1%TYPE;
T1 VARCHAR := 'sourceTable';
query_value VARCHAR ;
BEGIN
SELECT col2 INTO cm_tableName FROM T1 WHERE col1 = var1 ;
EXECUTE query_value;
RETURN cm_tableName;
END;
$$ language 'plpgsql' IMMUTABLE STRICT;
select test('abc');
Failed.
Also, tried "SELECT col2 INTO cm_tableName FROM || T1 WHERE col1 = ||
var1 " and
"SELECT col2 INTO cm_tableName FROM || T1 || WHERE col1 = || var1"
Failed as well.
T1 and var1 both are variables, may I how to use variables in a "select
... into " query please?
Thanks a lot,
Ying
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(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] in PlPgSQL function, how to use variable in a "select ...
am 17.03.2006, um 14:23:57 -0500 mailte Emi Lu folgendes:
> Does not work either, the whole function is:
>
> create table t1(col1 varchar(3), col2 varchar(100));
> insert into t1 values('001', 'Result 1');
> insert into t1 values('002', 'Result 2');
> insert into t1 values('003', 'Result 3');
>
> CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
> DECLARE
>col1_valueALIAS FOR $1;
> cm_tableName st1_legend.code_map_tablename%TYPE;
> lengendTableNameVARCHAR := 't1';
>query_valueVARCHAR ;
> BEGIN
>
>SELECT INTO cm_tableName col2 FROM lengendTableName WHERE col1 =
> col1_value ;
This can't work, read the docu:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
You should build a string with your SQL and EXECUTE this string.
HTH, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] in PlPgSQL function, how to use variable in a "select ...
Does not work either, the whole function is:
create table t1(col1 varchar(3), col2 varchar(100));
insert into t1 values('001', 'Result 1');
insert into t1 values('002', 'Result 2');
insert into t1 values('003', 'Result 3');
CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
col1_valueALIAS FOR $1;
cm_tableName st1_legend.code_map_tablename%TYPE;
lengendTableNameVARCHAR := 't1';
query_valueVARCHAR ;
BEGIN
SELECT INTO cm_tableName col2 FROM lengendTableName WHERE col1 =
col1_value ;
This can't work, read the docu:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
You should build a string with your SQL and EXECUTE this string.
Thank you Andreas. Unfortunately it did not work. maybe I made something
wrong?
drop table t1;
create table t1(col1 varchar(3), col2 varchar(100));
insert into t1 values('001', 'Result 1');
insert into t1 values('002', 'Result 2');
insert into t1 values('003', 'Result 3');
CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
DECLARE
col1_valueALIAS FOR $1;
cm_tableName st1_legend.code_map_tablename%TYPE;
lengendTableNameVARCHAR := 't1';
query_valueVARCHAR ;
BEGIN
query_value := 'SELECT col2 FROM lengendTableName WHERE col1 = \'' ||
col1_value || '\'';
EXECUTE query_value INTO cm_tableName;
RETURN cm_tableName;
END;
$$ language 'plpgsql' IMMUTABLE STRICT;
select test('001');
Error:
ERROR: syntax error at or near "$2" at character 20
QUERY: SELECT $1 INTO $2
CONTEXT: PL/pgSQL function "test" line 9 at execute statement
LINE 1: SELECT $1 INTO $2
I am using postgresql 8.0.1, and I am afraid that 8.0 does not support
"excecute ... into "
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html
I will try to use cursor.
Thank you very much for all your help anyway.
Ying
---(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] in PlPgSQL function, how to use variable in a "select ...
On Friday 17 March 2006 15:33, Emi Lu wrote:
> >>Does not work either, the whole function is:
> >>
> >>create table t1(col1 varchar(3), col2 varchar(100));
> >>insert into t1 values('001', 'Result 1');
> >>insert into t1 values('002', 'Result 2');
> >>insert into t1 values('003', 'Result 3');
> >>
> >>CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
> >>DECLARE
> >> col1_valueALIAS FOR $1;
> >>cm_tableName st1_legend.code_map_tablename%TYPE;
> >>lengendTableNameVARCHAR := 't1';
> >> query_valueVARCHAR ;
> >>BEGIN
> >>
> >> SELECT INTO cm_tableName col2 FROM lengendTableName WHERE col1 =
> >>col1_value ;
> >
> >This can't work, read the docu:
> >http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLP
> >GSQL-STATEMENTS-EXECUTING-DYN
> >
> >You should build a string with your SQL and EXECUTE this string.
>
> Thank you Andreas. Unfortunately it did not work. maybe I made something
> wrong?
>
> drop table t1;
> create table t1(col1 varchar(3), col2 varchar(100));
> insert into t1 values('001', 'Result 1');
> insert into t1 values('002', 'Result 2');
> insert into t1 values('003', 'Result 3');
>
> CREATE OR REPLACE FUNCTION test(VARCHAR) RETURNS VARCHAR AS $$
> DECLARE
> col1_valueALIAS FOR $1;
> cm_tableName st1_legend.code_map_tablename%TYPE;
> lengendTableNameVARCHAR := 't1';
> query_valueVARCHAR ;
> BEGIN
> query_value := 'SELECT col2 FROM lengendTableName WHERE col1 = \'' ||
> col1_value || '\'';
>
> EXECUTE query_value INTO cm_tableName;
>
> RETURN cm_tableName;
> END;
> $$ language 'plpgsql' IMMUTABLE STRICT;
> select test('001');
>
This function would work on 8.1, provided you created the sql statement
correctly:
query_value := 'SELECT col2 FROM ' ||lengendTableName||' WHERE col1 = \'' ||
col1_value || '\'';
>
> I am using postgresql 8.0.1, and I am afraid that 8.0 does not support
> "excecute ... into "
>
In which case you could use:
FOR cm_tableName IN EXECUTE query_value LOOP
RETURN cm_tableName
END LOOP
which is a little hacky, though you could use a second variable for assignment
if you felt strongly about it.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] update before drop causes OID problems in transaction?
Not sure if this is the best list for this issue, but I ran into something that I thought should work inside a transaction, but obviously PostgreSQL thought otherwise. Postgres version is 8.1.3. The transaction I wrote is basically: BEGIN; DROP RULE foo_audit_no_update ON foo_audit; UPDATE foo_audit SET modified_by = 1 WHERE modified_by IS NULL; I then copy the data out of foo_audit into some temporary tables, drop foo_audit, alter table foo as I want it to be, then recreate foo_audit and all the triggers, functions, copy the data back into foo_audit from the temp tables and then COMMIT; After commit, I get a lovely: ERROR: could not open relation with OID x Is this expected? To solve this, I simply moved my initial update outside the transaction. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] update before drop causes OID problems in transaction?
Jeff Frost <[EMAIL PROTECTED]> writes: > After commit, I get a lovely: > ERROR: could not open relation with OID x Could we see a complete test case, rather than handwaving? I'd expect some issues like this if you were using any prepared statements or plpgsql functions with non-EXECUTEd queries involving the dropped table, but your description doesn't mention either of those risk factors. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] update before drop causes OID problems in transaction?
On Fri, 17 Mar 2006, Tom Lane wrote: Jeff Frost <[EMAIL PROTECTED]> writes: After commit, I get a lovely: ERROR: could not open relation with OID x Could we see a complete test case, rather than handwaving? I'd expect some issues like this if you were using any prepared statements or plpgsql functions with non-EXECUTEd queries involving the dropped table, but your description doesn't mention either of those risk factors. Tom, it's for a client, so let me see if they'll allow me to post the transaction, if not, I'll have to write something equivalent. More later. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(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] update before drop causes OID problems in transaction?
On Fri, 17 Mar 2006, Jeff Frost wrote:
Could we see a complete test case, rather than handwaving? I'd expect
some issues like this if you were using any prepared statements or
plpgsql functions with non-EXECUTEd queries involving the dropped table,
but your description doesn't mention either of those risk factors.
Tom, it's for a client, so let me see if they'll allow me to post the
transaction, if not, I'll have to write something equivalent. More later.
Alright, they are fine with me sharing the SQL, so here goes:
I suspect I've answered my own question while preparing the test case. Is it
the use of pg_get_serial_sequence at the bottom of the transaction? If so,
why does it only have a problem when there is an update to credit_card_audit
in the transaction?
If I'm looking at this correctly, the OID referenced is credit_card_audit:
SELECT * from pg_class where relfilenode = 29976142;
relname | relnamespace | reltype | relowner | relam | relfilenode
| reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl
---+--+--+--+---+-+---+--+---+---+---+-+-+-+--+---+-+--+--+-+++-++
credit_card_audit | 2200 | 29976143 |16387 | 0 |29976142
| 0 | 133 | 3329 | 29976148 | 0 | t
| f | r |9 | 1 | 6 |0 |
0 | 0 | t | t | t | f |
(1 row)
Below is the transaction and following that is a \d of the credit_card and
credit_card_audit tables:
BEGIN;
DROP RULE credit_card_audit_no_update ON credit_card_audit;
-- We have a not null constraint in the new table
-- Without this UPDATE, the transaction is fine
-- but with it, we get the ERROR: could not open relation
-- with OID 29976142
UPDATE credit_card_audit SET modified_by = 1
WHERE modified_by IS NULL;
CREATE TEMP TABLE ca_common (LIKE credit_card_audit) ON COMMIT DROP;
INSERT INTO ca_common SELECT * FROM credit_card_audit;
ALTER TABLE ca_common DROP COLUMN credit_card_old;
ALTER TABLE ca_common DROP COLUMN credit_card_new;
CREATE TEMP TABLE ca_old (credit_card_audit_id INTEGER, LIKE credit_card) ON
COMMIT DROP;
ALTER TABLE ca_old ALTER column id drop not null;
ALTER TABLE ca_old ALTER column account_id drop not null;
ALTER TABLE ca_old ALTER column profile_id drop not null;
ALTER TABLE ca_old ALTER column expires drop not null;
ALTER TABLE ca_old ALTER column credit_card_type drop not null;
ALTER TABLE ca_old ALTER column billing_name drop not null;
INSERT INTO ca_old
SELECT
credit_card_audit_id,
(credit_card_old).*
FROM credit_card_audit;
CREATE TEMP TABLE ca_new (credit_card_audit_id INTEGER, LIKE credit_card) ON
COMMIT DROP;
ALTER TABLE ca_new ALTER column id drop not null;
ALTER TABLE ca_new ALTER column account_id drop not null;
ALTER TABLE ca_new ALTER column profile_id drop not null;
ALTER TABLE ca_new ALTER column expires drop not null;
ALTER TABLE ca_new ALTER column credit_card_type drop not null;
ALTER TABLE ca_new ALTER column billing_name drop not null;
INSERT INTO ca_new
SELECT
credit_card_audit_id,
(credit_card_new).*
FROM credit_card_audit;
DROP TRIGGER audit_credit_card ON credit_card;
DROP TABLE credit_card_audit;
DROP VIEW cc_with_id_view;
ALTER TABLE credit_card DROP COLUMN billing_name;
-- recreate credit_card_audit
CREATE TABLE public.credit_card_audit (
credit_card_audit_id BIGSERIAL PRIMARY KEY
, actor TEXT NOT NULL DEFAULT current_user
, action TEXT NOT NULL CHECK(action IN ('INSERT', 'UPDATE', 'DELETE'))
, credit_card_action_time TIMESTAMP WITH TIME ZONE NOT NULL
DEFAULT CURRENT_TIMESTAMP
, event_type TEXT
, modified_by INTEGER NOT NULL REFERENCES accounts_basics(id)
, credit_card_old public.credit_card
, credit_card_new public.credit_card
);
COMMENT ON TABLE public.credit_card_audit IS $$
Timestamp, old and new column sets for auditing.
This gets written on any change to public.credit_card.
It was created via
/home/jeff.frost/p4/ops-dbtools/set_up_audit.pl -t credit_card -e
modified_by=INTEGER NOT NULL REFERENCES accounts_basics(id) -e event_type=TEXT
$$;
CREATE RULE credit_card_audit_no_delete AS
ON DELETE TO public.credit_card_audit
DO INSTEAD NOTHING;
CREATE RULE credit_card_audit_no_update AS
ON UPDATE TO public.credit_card_audit
DO INSTEAD NOTHING;
CREATE INDEX credit_card_audit_event_type_idx
ON public.credit_card_audit(event_type);
CREATE INDEX credit_card_audit_modified_by_idx
ON public.credit_card_audit(modified_by);
CREATE OR REPLACE FUNCTION public.audit_credit_card ()
RETURNS TRIGG
Re: [SQL] update before drop causes OID problems in transaction?
Jeff Frost <[EMAIL PROTECTED]> writes: > I suspect I've answered my own question while preparing the test case. Is it > the use of pg_get_serial_sequence at the bottom of the transaction? If so, > why does it only have a problem when there is an update to credit_card_audit > in the transaction? No, I think it's that you've got a plpgsql trigger function that contains queries referring to credit_card_audit. Dropping and recreating that table invalidates plpgsql's cached plans for those queries. We do have in mind to fix this (Neil Conway was poking at it, last I heard) but it won't happen before 8.2 at the earliest. In the meantime I'm wondering why you are insistent on dropping and recreating credit_card_audit, as opposed to something less invasive like TRUNCATE. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] update before drop causes OID problems in transaction?
Jeff Frost <[EMAIL PROTECTED]> writes: > On Sat, 18 Mar 2006, Tom Lane wrote: >> No, I think it's that you've got a plpgsql trigger function that >> contains queries referring to credit_card_audit. Dropping and >> recreating that table invalidates plpgsql's cached plans for those >> queries. > Is that the case whether the triggers are executed or not? If the trigger function hasn't ever been executed in the current session, it wouldn't have a cached plan ... but I suspect you meant "if it hasn't been executed in the current transaction", and that doesn't help. > However, we drop that trigger before > dropping credit_card_audit, so I'd think that would be ok. IIRC you'd have to drop the underlying plpgsql function, not only the trigger object that connects the function to a table. We cache stuff with respect to the function. 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
Re: [SQL] update before drop causes OID problems in transaction?
On Sat, 18 Mar 2006, Tom Lane wrote: No, I think it's that you've got a plpgsql trigger function that contains queries referring to credit_card_audit. Dropping and recreating that table invalidates plpgsql's cached plans for those queries. Is that the case whether the triggers are executed or not? There aren't any triggers on credit_card_audit, but credit_card has the audit_credit_card trigger which calls a plpgsql function. However, we drop that trigger before dropping credit_card_audit, so I'd think that would be ok. Also, we aren't modifying data in credit_card, so I wouldn't think that trigger would fire anyway. Of course, I probably am missing something here. We do have in mind to fix this (Neil Conway was poking at it, last I heard) but it won't happen before 8.2 at the earliest. In the meantime I'm wondering why you are insistent on dropping and recreating credit_card_audit, as opposed to something less invasive like TRUNCATE. I inherited this procedure from the previous DBA and hadn't looked at streamlining until now. I would guess it's because we have a script which generates the SQL responsible for setting up the audit table and associated trigger, constraints and functions..thus making it easier to just drop and recreate the table with the automatically generated SQL. The procedure has worked well in the past, but this is the first time I needed to incorporate an update due to changing a NOT NULL constraint. I didn't think this to be the expected behavior for this query, so I thought I'd post and see whether I was thinking along the wrong lines. If this is the expected behavior, then TRUNCATE...ALTER TABLE appears like the way to go in the future. Thanks, as always, for the info! -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(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] update before drop causes OID problems in transaction?
On Sat, 18 Mar 2006, Tom Lane wrote: Is that the case whether the triggers are executed or not? If the trigger function hasn't ever been executed in the current session, it wouldn't have a cached plan ... but I suspect you meant "if it hasn't been executed in the current transaction", and that doesn't help. well, actually, I sort of meant both, though of course I'd prefer the same transaction. I was actually calling this script via psql -f so it would only be that transaction in that one session. However, we drop that trigger before dropping credit_card_audit, so I'd think that would be ok. IIRC you'd have to drop the underlying plpgsql function, not only the trigger object that connects the function to a table. We cache stuff with respect to the function. I'll try that and see if that makes the difference, since we're recreating (create or replace) that function in that transaction anyway, but perhaps that needs to happen before the update. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] update before drop causes OID problems in transaction?
On Fri, 17 Mar 2006, Jeff Frost wrote: I'll try that and see if that makes the difference, since we're recreating (create or replace) that function in that transaction anyway, but perhaps that needs to happen before the update. I added this at the top of the transaction: DROP FUNCTION public.audit_credit_card (); and had to move the drop trigger above it, so the order looked like so: BEGIN; DROP RULE credit_card_audit_no_update ON credit_card_audit; DROP TRIGGER audit_credit_card ON credit_card; DROP FUNCTION public.audit_credit_card (); Same result: psql:transaction-test-case.sql:212: ERROR: could not open relation with OID 29976142 -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(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
