I would like another TG_* special variable to be available to a PL/pgSQL
trigger-function.
TG_COLUMNS_UPDATED
Its value would be NULL unless: TG_OP == ' UPDATE' and TG_LEVEL == 'ROW'
Data type == varbit
One bit for each column of the table that the trigger is created on.
1 means that
(version == 9.1)
In my PL/pgSQL stored functions,
I want to be able to distinguish which FK-constraint caused the
[foreign_key_violation] exception.
. . .
BEGIN
delete from MY_COOL_TABLE where id = 123 ;
EXCEPTION
WHEN foreign_key_violation THEN
CASE
WHEN (SQLERRM tell
(version == 9.1)
In my PL/pgSQL stored functions,
I want to be able to distinguish which FK constraint caused the
[foreign_key_violation] exception.
. . .
BEGIN
delete from MY_COOL_TABLE where id = 123 ;
EXCEPTION
WHEN foreign_key_violation THEN
CASE
WHEN (SQLERRM tell
Scenario: (not slony, it is home-grown replication)
A change on the Primary db is Captured and then Propagated to the Secondary db.
Then the change is Applied to the Secondary db, with [session_replication_role]
= 'replica'.
I agree that I don't want my "user triggers" to fire as part of the App
Today I learned that . . .
the firing of even "internally generated constraint triggers" is affected by
the value of [session_replication_role].
Sadly, I had previously assumed that such "internal" triggers did not care
about [s_r_r].
Also learned that . . .
when a FK constraint gets made, its
-- This works.
select
TRG.tgname, TFX.proname, pg_get_functiondef(TFX.oid) as fdef
from
pg_trigger TRG
inner join pg_proc TFX on TFX.oid = TRG.tgfoid
where
TRG.tgisinternal = true
-- This blows up. -- SQL state: 42809 -- ERROR: "array_agg" is an aggregate
function
select
T
Can anybody please point me to where this "difference of behavior" is
explained/documented ?
Thanks,
-dvs-
-- version = 9.1.3
do $$
declare
v_str char(10);
begin
v_str := 'abc' ;
raise info '%', concat(v_str, v_str) ;
raise info '%', v_str||v_str ;
end
$$;
INFO: abc abc
INFO: ab
Do EXPLAIN ANALYZE:
. . . only showing the bottom node . . .
-> Seq Scan on Y
(cost=0.00..37962.29 rows=876029 width=40)
(actual time=16.728..92555.945 rows=876002 loops=1)
Output: foo, bar
Buffers:
shared hit=146
read=29056
written=2325 !
"Total runtime: 375542.347 ms"
Then Do
select version()
"PostgreSQL 9.0.1 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit"
I ran the [check_bloat] query from check_postgres.pl (v 2.18.0) twice, doing a
VACUUM FULL in between:
-- the RS
db, schemaname, tablename, tups, pages, otta, tblo
select version()
"PostgreSQL 9.0.1 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit"
I ran the [check_bloat] query from check_postgres.pl (v 2.18.0) twice, doing a
VACUUM FULL in between:
-- the RS
db, schemaname, tablename, tups, pages, otta, tbloa
Please consider this plpgsql function:
= = = = = = = = = =
CREATE Or Replace FUNCTION fx_order_by ( )
RETURNS table( last_name text, first_name )
AS $eofx$
DECLARE
--
BEGIN
Return Query
select
lname, fname
from
my_table
order by
lname ASC
;
END;
$eofx$ LANGUAGE plpgsql;
= =
In postgresql.org/docs/9.1/static/transaction-iso.html I read
13.2.1. Read Committed Isolation Level
. . . two successive SELECT commands can see different data, even though they
are within a single transaction . . .
Please consider this code being executed by postgres:
= = = = = = = = = =
selec
ref = [ e1qzdjc-xv...@gemulon.postgresql.org ]
I note with interest that [deadlock_timeout] can be used as . . .
"a poor-man's deadlock priority mechanism: a transaction with a high
[deadlock_timeout] is less
likely to be chosen as the victim than one with a low [deadlock_timeout]"
I for on
From May 31, 2006; 12:03pm . . .
"It struck me that we are missing a feature that's fairly common in Unix
programs.
Perhaps COPY ought to have the ability to pipe its output to a shell command,
or read input from a shell command. "
Maybe something like:
COPY mytable TO '| gzip >/home/tgl/
14 matches
Mail list logo
