[GENERAL] TG_COLUMNS_UPDATED
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 the column was in the set clause of the update statement that made the trigger fire 0 means it was not I understand that CREATE TRIGGER already has UPDATE [ OF column_name [, ... ] ] Is this a relatively straightforward enhancement ? It would allow me to know whether various timestamp columns in the row were unlucky enough to have been set to the same exact value already existing in the table *versus* were simply not set by the UPDATE statement. Thanks, -dvs-
[GENERAL] parsing SQLERRM ?
(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 tells me it blew up because of FK X) THEN . . . ; WHEN (SQLERRM tells me it blew up because of FK Y) THEN . . . ; WHEN (SQLERRM tells me it blew up because of FK Z) THEN . . . ; END; WHEN others THEN raise; END; . . . Is a robust enough parsing of SQLERRM actually the best/only way to determine this ? -dvs-
[GENERAL] parsing the SQLERRM string
(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 tells me it blew up because of FK X) THEN . . . ; WHEN (SQLERRM tells me it blew up because of FK Y) THEN . . . ; WHEN (SQLERRM tells me it blew up because of FK Z) THEN . . . ; END; WHEN others THEN raise; END; . . . Is a robust enough parsing of SQLERRM actually the best way to do this ? If so, what assumptions can I make about the SQLERRM string ? When I set lc_messages = en_US.UTF-8' or 'fr_FR.UTF-8' or 'zh_CN.UTF-8' I get these messages: INFO: 23503: update or delete on table TBLONE violates foreign key constraint FK_XXX on table TBLTWO INFO: 23503: UPDATE ou DELETE sur la table TBLONE viole la contrainte de clé étrangère FK_XXX de la table TBLTWO 信息: 23503: 在 TBLONE 上的更新或删除操作违反了在 TBLTWO 上的外键约束 FK_XXX which seems to make any regex/parsing of SQLERRM real hard. Is anybody doing this ? Thanks, -dvs-
Re: [GENERAL] Change the default [tgenabled] for new internal triggers ?
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 Apply. But my email was about the internally generated constraint triggers which implement checking for Foreign Key Constraint violations. It is that checking that I want to be done on the Secondary. Should I not want such checking to be done ? Thanks, -dvs- -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, March 23, 2012 8:35 PM To: Sahagian, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Change the default [tgenabled] for new internal triggers ? david.sahag...@emc.com writes: Is the a way to configure Postgres such that tgenabled = ' A' automatically when the FK constraint gets made ? No. Why do you think that would be a good idea? ISTM it'd lead to the action being taken twice on the slave. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Change the default [tgenabled] for new internal triggers ?
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 pg_trigger.tgenabled = ' O'. QUESTION: Is the a way to configure Postgres such that tgenabled = ' A' automatically when the FK constraint gets made ? Or must I !un-automatedly! do a . . . alter table MYTABLEENABLE ALWAYS TRIGGER RI_ConstraintTrigger_18656 whenever somebody makes a FK ?!? Thanks, -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] usage of pg_get_functiondef() -- SQL state 42809
-- 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 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 and pg_get_functiondef(TFX.oid) = 'whatever' Can you help me understand why this blows up ? I am running PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] || versus concat( ), diff behavior
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: abcabc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.0 EXPLAIN Buffers: written=nnnn
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 EXPLAIN ANALYZE again: . . . only showing the bottom node . . . - Seq Scan on Y (cost=0.00..37962.29 rows=876029 width=40) (actual time=0.192..2972.127 rows=876002 loops=1) Output: foo, bar Buffers: shared hit=210 read=28992 Total runtime: 32913.884 ms In general, can you tell me why [written=2325] is displayed by the first EXPLAIN, but not the second EXPLAIN ? The query is a SELECT, so what is getting written ? Note that both EXPLAINs have the exact same plan. Bottom up is: Seq Scan on , Hash, Seq Scan on , Hash Join, HashAggregate I am wondering if it is a clue as to why the actual time is so different from one run to the next. The query is 2 tables joined, with a group by: select sum (case when X.hid is null and not Y.cntr = '0' then 1 else 0 end) as colp from x X inner join y Y on Y.coln = X.colm where X.some_id = 'aeiou' group by X.some_type Thanks, -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] not-always-full vacuuming in 9.0 ?
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, tbloat, wastedpages, wastedbytes, wastedsize, iname, itups, ipages, iotta, ibloat, wastedipages, wastedibytes, wastedisize -- I ran the first check_bloat mydb;myschema;MyTbl;131370;32863;28101;1.2;4762;39010304;39010304 bytes; idx_mytbl;131370;361;27313;0.0;0;0;0 bytes -- then I ran VACUUM FULL verbose analyze myschema.MyTbl ; INFO: vacuuming myschema.MyTbl INFO: analyzing myschema.MyTbl INFO: MyTbl: scanned 3 of 32795 pages, containing 120178 live rows and 0 dead rows; 3 rows in sample, 131375 estimated total rows Query returned successfully with no result in 12172 ms. -- then I ran the second check_bloat mydb;myschema;MyTbl;131375;32795;28102;1.2;4693;38445056;38445056 bytes; idx_mytbl;131376;509;27314;0.0;0;0;0 bytes ?? ?? ?? ?? ?? Can anybody suggest why wastedbytes did not go down to zero ? ?? ?? ?? ?? ?? Note that for another table, it does . . . mydb;myschema; otherTbl;49;20619;16887;1.2;3732;30572544;30572544 bytes; pk_otherTbl;49;4548;14073;0.3;0;0;0 bytes VACUUM FULL verbose analyze myschema.otherTbl ; INFO: vacuuming myschema.otherTbl INFO: analyzing myschema.otherTbl INFO: otherTbl : scanned 16667 of 16667 pages, containing 49 live rows and 0 dead rows; 3 rows in sample, 49 estimated total rows Query returned successfully with no result in 6781 ms. mydb;myschema; otherTbl;49;16667;16887;1.0;0;0;0 bytes; pk_otherTbl ;49;3607;14073;0.3;0;0;0 bytes Thanks, -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] not-always-full vacuuming in 9.0 ?
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, tbloat, wastedpages, wastedbytes, wastedsize, iname, itups, ipages, iotta, ibloat, wastedipages, wastedibytes, wastedisize -- I ran the first check_bloat mydb;myschema;MyTbl;131370;32863;28101;1.2;4762;39010304;39010304 bytes; idx_mytbl;131370;361;27313;0.0;0;0;0 bytes -- then I ran VACUUM FULL verbose analyze myschema.MyTbl ; INFO: vacuuming myschema.MyTbl INFO: analyzing myschema.MyTbl INFO: MyTbl: scanned 3 of 32795 pages, containing 120178 live rows and 0 dead rows; 3 rows in sample, 131375 estimated total rows Query returned successfully with no result in 12172 ms. -- then I ran the second check_bloat mydb;myschema;MyTbl;131375;32795;28102;1.2;4693;38445056;38445056 bytes; idx_mytbl;131376;509;27314;0.0;0;0;0 bytes Can anybody suggest why wastedbytes does not go down to zero ? Note that for another table, it does . . . mydb;myschema; otherTbl;49;20619;16887;1.2;3732;30572544;30572544 bytes; pk_otherTbl;49;4548;14073;0.3;0;0;0 bytes VACUUM FULL verbose analyze myschema.otherTbl ; INFO: vacuuming myschema.otherTbl INFO: analyzing myschema.otherTbl INFO: otherTbl : scanned 16667 of 16667 pages, containing 49 live rows and 0 dead rows; 3 rows in sample, 49 estimated total rows Query returned successfully with no result in 6781 ms. mydb;myschema; otherTbl;49;16667;16887;1.0;0;0;0 bytes; pk_otherTbl ;49;3607;14073;0.3;0;0;0 bytes Thanks, -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] order by, within a plpgsql fx
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; = = = = = = = = = = So, is this select statement's result set guaranteed to be ordered as specified by the [order by] coded within the function body ? = = = = = = = = = = select last_name, first_name from fx_order_by() ; = = = = = = = = = = Or, must I code another [order by] to be sure ? = = = = = = = = = = select last_name, first_name from fx_order_by() order by last_name ; = = = = = = = = = = Also, is the answer the same for a sql function ? Thanks, -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] successive select statements
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: = = = = = = = = = = select some_int from tableX union all select another_int from tableY ; = = = = = = = = = = Are these two select statements considered successive when isolation == Read Committed ? Or are they instead guaranteed to see the same snapshot of the database ? Also, please consider this code being executed by postgres: = = = = = = = = = = CREATE Or Replace FUNCTION fx_one() RETURNS setof integer AS $$ select some_int from tableX; $$ LANGUAGE 'sql'; CREATE Or Replace FUNCTION fx_two() RETURNS setof integer AS $$ select another_int from tableY; $$ LANGUAGE 'sql'; select * from fx_one() union all select * from fx_two() ; = = = = = = = = = = Are the two select statements inside fx_one and fx_two considered successive when isolation == Read Committed ? Or are they instead guaranteed to see the same snapshot of the database ? Thanks, -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] explicit deadlock-victim-priority mechanism
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 one, would definitely be interested in a more explicit priority mechanism. My use case is: We have some batch work processes, whose deadlock-victim-priority I would like to set to HIGH. (at the expense of smaller transactions who I would like to be the victim) -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY TO '|gzip /my/cool/file.gz'
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/mytable.dump.gz'; Is such a feature (ie being able to tell postgres to write a compressed file via COPY TO) being worked on ? -dvs- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
