Re: [PATCHES] pgkill for windows
Bruce Momjian said: I was hoping to avoid platform-specific binaries. Once pg_ctl is done in C, it can start/stop the postmaster, but not individual backends. Can we add a flag to pg_ctl so it can send arbitrary signals to processed on Win32? That would be best, I think. Ok, that makes sense. I am working on pg_ctl and hope to have a first cut in a day or two (everything is done except the startup code). cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Add error-checking to timestamp_recv
Stephen Frost [EMAIL PROTECTED] writes: * Bruce Momjian ([EMAIL PROTECTED]) wrote: Would you show an example of the invalid value this is trying to avoid? Well, the way I discovered the problem was by sending a timestamp in double format when the server was expecting one in int64 format. Most of the time, though, this sort of error would still yield a valid value that just failed to represent the timestamp value you wanted. I'm unsure that a range check is going to help much. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Add error-checking to timestamp_recv
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Bruce Momjian ([EMAIL PROTECTED]) wrote: Would you show an example of the invalid value this is trying to avoid? Well, the way I discovered the problem was by sending a timestamp in double format when the server was expecting one in int64 format. Most of the time, though, this sort of error would still yield a valid value that just failed to represent the timestamp value you wanted. I'm unsure that a range check is going to help much. I'm not sure I agree about the 'most of the time' comment- I havn't done extensive tests yet but I wouldn't be at all suprised if most of the time range around the current date, when stored as a double and passed to the database which is expecting an int64, would cause the problem. The issue isn't about the wrong date being shown or anything, it's that the database accepts the value but then throws errors whenever you try to view the table. The intent of the patch was to use the exact same logic to test if the date is valid on the incoming side as is used to test the date before displaying it. This would hopefully make it impossible for someone to run into this problem in the future. It would also make it much clearer to the programmer that the date he's passing is bad and not that there's some corruption happening in the database after the date is accepted. Stephen signature.asc Description: Digital signature
Re: [PATCHES] Add error-checking to timestamp_recv
Stephen Frost wrote: -- Start of PGP signed section. * Bruce Momjian ([EMAIL PROTECTED]) wrote: Would you show an example of the invalid value this is trying to avoid? Well, the way I discovered the problem was by sending a timestamp in double format when the server was expecting one in int64 format. This is when using the binary data method for timestamps. I'll generate a small example program/schema later today and post it to the list. So you are passing the data via binary COPY or a C function? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Add error-checking to timestamp_recv
* Bruce Momjian ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: -- Start of PGP signed section. * Bruce Momjian ([EMAIL PROTECTED]) wrote: Would you show an example of the invalid value this is trying to avoid? Well, the way I discovered the problem was by sending a timestamp in double format when the server was expecting one in int64 format. This is when using the binary data method for timestamps. I'll generate a small example program/schema later today and post it to the list. So you are passing the data via binary COPY or a C function? Sorry I wasn't clear, it's using libpq and binary data using an 'insert' statement. The code looks something like this: PQexec(connection,prepare addrow (timestamp) as insert into mytable values ($1)); lengths[0] = sizeof(double); formats[0] = 1; *(double*)(values[0]) = tv_sec - ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_DATE) * 86400) + (tv_sec / 100.00); PQexecPrepared(connection,addrow,1,(void*)values,lengths,formats,0); While the new code is something like: int64_t pg_timestamp; PQexec(connection,prepare addrow (timestamp) as insert into mytable values ($1)); lengths[0] = sizeof(int64_t); formats[0] = 1; pg_timestamp = ((tv_sec - ((POSTGRES_EPOCH_JDATE - UNIX_EPOCH_JDATE) * 86400)) * (int64_t)100) + tv_usec; *(int64_t*)(values[0]) = bswap_64(pg_timestamp); PQexecPrepared(connection,addrow,1,(void*)values,lengths,formats,0); I'll see about writing up a proper test case/schema. Looks like I'm probably most of the way there at this point, really. ;) Stephen signature.asc Description: Digital signature
Re: [PATCHES] Add error-checking to timestamp_recv
* Bruce Momjian ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: I'll see about writing up a proper test case/schema. Looks like I'm probably most of the way there at this point, really. ;) I wasn't aware you could throw binary values into the timestamp fields like that. I thought you needed to use a C string for the value. Does PREPARE bypass that for some reason? I don't think so.. As I recall, I think I might have had it set up w/o using a prepare before and it was working but I'm not sure. It's certainly very useful and lets me bypass *alot* of overhead (converting to a string and then making the database convert back...). The one complaint I do have is that I don't see a way to pass a timestamp w/ an explicit timezone in binary format into a table which has a 'timestamp with timezone' field. I can pass a binary timestamp into a 'timestamp with timezone' field, but it's interpreted as UTC or the local timezone (can't remember which atm). Stephen signature.asc Description: Digital signature
Re: [PATCHES] Add error-checking to timestamp_recv
Stephen Frost wrote: -- Start of PGP signed section. * Bruce Momjian ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: I'll see about writing up a proper test case/schema. Looks like I'm probably most of the way there at this point, really. ;) I wasn't aware you could throw binary values into the timestamp fields like that. I thought you needed to use a C string for the value. Does PREPARE bypass that for some reason? I don't think so.. As I recall, I think I might have had it set up w/o using a prepare before and it was working but I'm not sure. It's certainly very useful and lets me bypass *alot* of overhead (converting to a string and then making the database convert back...). Considering all the other things the database is doing, I can't imagine that would be a measurable improvement. The one complaint I do have is that I don't see a way to pass a timestamp w/ an explicit timezone in binary format into a table which has a 'timestamp with timezone' field. I can pass a binary timestamp into a 'timestamp with timezone' field, but it's interpreted as UTC or the local timezone (can't remember which atm). I still do not understand how this is working. It must be using our fast path as part of prepare. What language is you client code? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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: [PATCHES] Add error-checking to timestamp_recv
* Bruce Momjian ([EMAIL PROTECTED]) wrote: Considering all the other things the database is doing, I can't imagine that would be a measurable improvement. It makes it easier on my client program too which is listening to an ethernet interface and trying to process all of the packets coming in off of it and putting timestamps and header information into the database. The table in the database doesn't have any constraints or primary keys on it or anything, pretty much as simple as I could make it. :) The one complaint I do have is that I don't see a way to pass a timestamp w/ an explicit timezone in binary format into a table which has a 'timestamp with timezone' field. I can pass a binary timestamp into a 'timestamp with timezone' field, but it's interpreted as UTC or the local timezone (can't remember which atm). I still do not understand how this is working. It must be using our fast path as part of prepare. What language is you client code? It's just plain ol' C. It's a pretty short/simple program, really. It uses libpcap to listen to the interface, checks the type of packet (ethernet, IP, UDP/TCP, etc), copies the binary header values into the structure which it then passes to PQexecPrepared. It's kind of amazing under 2.6, you can actually calculate the delay and bandwidth pretty accurately through a network (7 'backbone' nodes, each with a backbone router, an edge router, and an access router, all in a lab) by listening on two interfaces, one on each side to calculate one-way propagation time. Stephen signature.asc Description: Digital signature
Re: [PATCHES] Add error-checking to timestamp_recv
Bruce Momjian [EMAIL PROTECTED] writes: I wasn't aware you could throw binary values into the timestamp fields like that. I thought you needed to use a C string for the value. This facility was added in 7.4 as part of the wire-protocol overhaul. It's nothing directly to do with PREPARE; you could get the same result with no prepared statement using PQexecParams. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Add error-checking to timestamp_recv
* Bruce Momjian ([EMAIL PROTECTED]) wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I wasn't aware you could throw binary values into the timestamp fields like that. I thought you needed to use a C string for the value. This facility was added in 7.4 as part of the wire-protocol overhaul. It's nothing directly to do with PREPARE; you could get the same result with no prepared statement using PQexecParams. Ah, no wonder I had not seen that before. So, I guess the issue is how much error checking do we want to have for these binary values. I was a little disturbed to hear he could insert data he couldn't later view. How many datatype have this issue? I don't think that many do.. A number of them already check incoming values where it's possible for them to not be valid. For example, 'macaddr' accepts all possible binary values, 'inet' does error checking on input. Binary timestamps were the only place I found in the work I was doing where this could happen and I managed to mess up most of the fields in one way or another before I figured it all out. :) Stephen signature.asc Description: Digital signature
Re: [PATCHES] Add error-checking to timestamp_recv
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I wasn't aware you could throw binary values into the timestamp fields like that. I thought you needed to use a C string for the value. This facility was added in 7.4 as part of the wire-protocol overhaul. It's nothing directly to do with PREPARE; you could get the same result with no prepared statement using PQexecParams. Ah, no wonder I had not seen that before. So, I guess the issue is how much error checking do we want to have for these binary values. I was a little disturbed to hear he could insert data he couldn't later view. How many datatype have this issue? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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: [PATCHES] Add error-checking to timestamp_recv
Stephen Frost [EMAIL PROTECTED] writes: How many datatype have this issue? I don't think that many do.. A number of them already check incoming values where it's possible for them to not be valid. In general we do check incoming binary values to ensure minimal validity. I think when I did timestamp_recv I was thinking it was just like int8 or float8 (respectively), in that any bit pattern is potentially legal; I had forgotten about the range restrictions. I haven't looked at the details of Stephen's patch (and can't till the archives site comes back up) but the idea is probably sound. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Add error-checking to timestamp_recv
I wrote: In general we do check incoming binary values to ensure minimal validity. I think when I did timestamp_recv I was thinking it was just like int8 or float8 (respectively), in that any bit pattern is potentially legal; I had forgotten about the range restrictions. I haven't looked at the details of Stephen's patch (and can't till the archives site comes back up) but the idea is probably sound. Having looked at it, I don't like the patch as-is; it misses timestamptz_recv and doesn't handle the boundary condition correctly for the HasCTZSet case. However the details of the latter are likely to change completely once we finish adopting src/timezone. I'll make a note to do something with this issue after the TZ patch is in. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PATCHES] Disabling triggers / constraints
After trying to do some custom dumping/restoring, and having to resort
to the awful trick of changing the trigger counts on the catalog (the
stuff pg_dump adds), decided to add a couple little variables to control
disabling constraints and triggers. Added them to guc.c so that they
show up in the SET/SHOW list, and added checks accordingly (always at
top level, to avoid unnecessary function calls and loops). Variables are
'disable_constraints' and 'disable_triggers' and they are false by
default. I find it quite useful for loading large sets of data (I make
heavy use of CHECK constraints).
I am unsure wether it is right to send such an uncalled-for patch, but
seeing as this is such a simple thing that can be quite useful (large
volumes of data), I thought it could be useful.
(apply with patch -p0 from the root distro directory)
Cheers
- Jorge Pereira
*** src/backend/utils/misc/guc.c.orig 2004-05-19 18:52:08.718580424 +0100
--- src/backend/utils/misc/guc.c2004-05-19 18:50:46.162130912 +0100
***
*** 131,136
--- 131,140
int log_min_duration_statement = -1;
+ /* Control disabling of triggers (BS,AS,BR and AR) and constraints (useful on mass
insert from dumps)*/
+ bool disable_constraints;
+ bool disable_triggers;
+
/*
* These variables are all dummies that don't do anything, except in some
*** src/backend/executor/execMain.c.orig2004-05-19 18:53:14.251617888 +0100
--- src/backend/executor/execMain.c 2004-05-19 18:53:19.501819736 +0100
***
*** 90,95
--- 90,99
evalPlanQual *priorepq);
static void EvalPlanQualStop(evalPlanQual *epq);
+
+ extern bool disable_constraints;
+ extern bool disable_triggers;
+
/* end of local decls */
***
*** 1063,1068
--- 1067,1073
/*
* Process BEFORE EACH STATEMENT triggers
*/
+ if ( !disable_triggers )
switch (operation)
{
case CMD_UPDATE:
***
*** 1281,1286
--- 1286,1292
/*
* Process AFTER EACH STATEMENT triggers
*/
+ if ( !disable_triggers )
switch (operation)
{
case CMD_UPDATE:
***
*** 1379,1385
resultRelationDesc = resultRelInfo-ri_RelationDesc;
/* BEFORE ROW INSERT Triggers */
! if (resultRelInfo-ri_TrigDesc
resultRelInfo-ri_TrigDesc-n_before_row[TRIGGER_EVENT_INSERT] 0)
{
HeapTuple newtuple;
--- 1385,1391
resultRelationDesc = resultRelInfo-ri_RelationDesc;
/* BEFORE ROW INSERT Triggers */
! if ( !disable_triggers resultRelInfo-ri_TrigDesc
resultRelInfo-ri_TrigDesc-n_before_row[TRIGGER_EVENT_INSERT] 0)
{
HeapTuple newtuple;
***
*** 1405,1411
/*
* Check the constraints of the tuple
*/
! if (resultRelationDesc-rd_att-constr)
ExecConstraints(resultRelInfo, slot, estate);
/*
--- 1411,1417
/*
* Check the constraints of the tuple
*/
! if ( !disable_constraints resultRelationDesc-rd_att-constr )
ExecConstraints(resultRelInfo, slot, estate);
/*
***
*** 1431,1437
ExecInsertIndexTuples(slot, (tuple-t_self), estate, false);
/* AFTER ROW INSERT Triggers */
! ExecARInsertTriggers(estate, resultRelInfo, tuple);
}
/*
--- 1437,1444
ExecInsertIndexTuples(slot, (tuple-t_self), estate, false);
/* AFTER ROW INSERT Triggers */
! if ( !disable_triggers )
! ExecARInsertTriggers(estate, resultRelInfo, tuple);
}
/*
***
*** 1458,1464
resultRelationDesc = resultRelInfo-ri_RelationDesc;
/* BEFORE ROW DELETE Triggers */
! if (resultRelInfo-ri_TrigDesc
resultRelInfo-ri_TrigDesc-n_before_row[TRIGGER_EVENT_DELETE] 0)
{
booldodelete;
--- 1465,1471
resultRelationDesc = resultRelInfo-ri_RelationDesc;
/* BEFORE ROW DELETE Triggers */
! if ( !disable_triggers resultRelInfo-ri_TrigDesc
resultRelInfo-ri_TrigDesc-n_before_row[TRIGGER_EVENT_DELETE] 0)
{
booldodelete;
***
*** 1525,1531
*/
/* AFTER ROW DELETE Triggers */
! ExecARDeleteTriggers(estate, resultRelInfo, tupleid);
}
/*
--- 1532,1539
*/
/* AFTER ROW DELETE Triggers */
! if ( !disable_triggers )
! ExecARDeleteTriggers(estate, resultRelInfo, tupleid);
Re: [PATCHES] Disabling triggers / constraints
Jorge Pereira [EMAIL PROTECTED] writes: ... decided to add a couple little variables to control disabling constraints and triggers. I'm not of the opinion that we actually want any such thing, as it's a blatant violation of the fundamental concept of data integrity. But in any case not with such poor control over which triggers get suppressed. A per-table setting with appropriate permissions checks might possibly be acceptable. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] Configuration patch
[ Will apply with adjustment, removing tablespaces. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- [EMAIL PROTECTED] wrote: This patch incorporates a number of changes suggested by the group. The purpose of this patch is to move postgresql to a position where all configuration options are specified in one place. The postgresql.conf file could completely document a postgresql environment. It adds this functionality: The -D' option will work as it always has if it is set to a standard postgresql database cluster directory. If it is set to a postgresql.conf file, it will use that file for configuration. If it is set to a directory which is not a cluster directory, i.e. /somepath/etc it will look for pg_hba.conf, pg_ident.conf, and postgresql.conf there. For postgresql to work only with a configuration file, some options have been added: include = '/etc/postgres/debug.conf' pgdata = '/vol01/postgres' hba_conf = '/etc/postgres/pg_hba_conf' ident_conf = '/etc/postgres/pg_ident.conf' runtime_pidfile = '/var/run/postgresql.conf' tablespace = '/somevol/somepath' include allows files with configuration parameters to be included. pgdata (used to be data_dir in old patch) tells PostgreSQL where it's database cluster directory is located. hba_conf tells PostgreSQL where to find pg_hba.conf file. ident_conf tells PostgreSQL where to find pg_ident.conf. runtime_pidfile tells postgres to write it's PID to a file that would be used by external applications. It is *NOT* the pid file which postgresql uses. tablespace allows postgresql to use alternate locations without environment variables. Using SIGHUP, tablespaces are reloaded. This allows you to add tablespaces to a running PostgreSQL process. (I know this has a limited lifetime, but it may make CREATE DATABASE ... WITH LOCATION a little bit more sane in the meantime. [ Attachment, skipping... ] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Disabling triggers / constraints
Tom Lane wrote: Jorge Pereira [EMAIL PROTECTED] writes: ... decided to add a couple little variables to control disabling constraints and triggers. I'm not of the opinion that we actually want any such thing, as it's a blatant violation of the fundamental concept of data integrity. I can understand your concerns. But for the sake of context for context, here's an example of the code generated by pg_dump --disable-triggers: | -- Disable triggers | UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 'table_1'::pg_catalog.regclass; | -- INSERT / UPDATE statements; | -- Enable triggers | UPDATE pg_catalog.pg_class SET reltriggers = (SELECT pg_catalog.count(*) FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid = 'table_1'::pg_catalog.regclass; What I propose is | SET disable_triggers=1; | -- INSERT / UPDATE statements; | SET disable_triggers=0; This is not an option for daily use, just something that can be set to allow large volumes of data that is known to be conforming to be put into the database. It is critical for datawarehousing operations, where large volumes of data (on the TB scale) already processed and validated need to be put into the database. It is quite useful also for situations where checks depend on the existence of data in the database. I can put forward a few examples if it's deemed appropriate. :) In comparison, most DBs I've experienced with (Oracle, MySQL and argh MSSQL) have some way of disablling integrity checks and triggers (mainly for loading large sets of data known to be good). I don't see a need to do it on a per-table basis, seeing as this is mostly a per-datablock need - I couldn't think of a situation where enabling it only on one table would be benefitial, as that would imply that some of tha data you are inputing might not be conforming - which in turn means you shouldn't even be using this. On the other hand, you're absolutely right in that this is clearly something that should be done only by the database owner. a) would something similar be considered if such permission check was added (for owner only)? b) would it be considered only if changeable on a per-table basis? I'm new here. :) I hope I don't come across as someone trying to force his view of things, really just trying to pass on the experience I've had before, and which led me to the despair of having to go and tweak code. ;) Good thing of OS that I could. Cheers - Jorge Pereira ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Disabling triggers / constraints
Yes, agreed. I think we decided that super-user-only could disable trigger on a global basis. I prevent folks from mucking with the system tables to do it. --- Jorge Pereira wrote: Tom Lane wrote: Jorge Pereira [EMAIL PROTECTED] writes: ... decided to add a couple little variables to control disabling constraints and triggers. I'm not of the opinion that we actually want any such thing, as it's a blatant violation of the fundamental concept of data integrity. I can understand your concerns. But for the sake of context for context, here's an example of the code generated by pg_dump --disable-triggers: | -- Disable triggers | UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 'table_1'::pg_catalog.regclass; | -- INSERT / UPDATE statements; | -- Enable triggers | UPDATE pg_catalog.pg_class SET reltriggers = (SELECT pg_catalog.count(*) FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid = 'table_1'::pg_catalog.regclass; What I propose is | SET disable_triggers=1; | -- INSERT / UPDATE statements; | SET disable_triggers=0; This is not an option for daily use, just something that can be set to allow large volumes of data that is known to be conforming to be put into the database. It is critical for datawarehousing operations, where large volumes of data (on the TB scale) already processed and validated need to be put into the database. It is quite useful also for situations where checks depend on the existence of data in the database. I can put forward a few examples if it's deemed appropriate. :) In comparison, most DBs I've experienced with (Oracle, MySQL and argh MSSQL) have some way of disablling integrity checks and triggers (mainly for loading large sets of data known to be good). I don't see a need to do it on a per-table basis, seeing as this is mostly a per-datablock need - I couldn't think of a situation where enabling it only on one table would be benefitial, as that would imply that some of tha data you are inputing might not be conforming - which in turn means you shouldn't even be using this. On the other hand, you're absolutely right in that this is clearly something that should be done only by the database owner. a) would something similar be considered if such permission check was added (for owner only)? b) would it be considered only if changeable on a per-table basis? I'm new here. :) I hope I don't come across as someone trying to force his view of things, really just trying to pass on the experience I've had before, and which led me to the despair of having to go and tweak code. ;) Good thing of OS that I could. Cheers - Jorge Pereira ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Disabling triggers / constraints
Yes, agreed. I think we decided that super-user-only could disable trigger on a global basis. I prevent folks from mucking with the system tables to do it. It should be a per-table thing: ALTER TABLE blah [ DISABLE | ENABLE ] [ALL | FOREIGN KEY ] TRIGGERS; Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Disabling triggers / constraints
Christopher Kings-Lynne wrote: Yes, agreed. I think we decided that super-user-only could disable trigger on a global basis. I prevent folks from mucking with the system tables to do it. It should be a per-table thing: ALTER TABLE blah [ DISABLE | ENABLE ] [ALL | FOREIGN KEY ] TRIGGERS; Doing it with alter table seems to imply that the change is permanent (eg, the table loses checking), whereas that is most certainly not what is wanted. With a SET variable it lasts only for the session, and doesn't have to be reset manually. Assuming one wants the setting to last, as far as I can think of, an alter table would also mean either a) doing the aforementioned juggling with setting number of triggers to 0 and recounting when enabling or b) adding a new field to tables on the catalog. Solution a) I think is a nasty hack, and doesn't reflect the fact that the table *does* have triggers (which just happen to be disabled), whereas solution a) probably implies adding a field to tables in the catalog - and it's probably too much trouble? On the other hand, alter table has the advantage of being far more intuitive. Any other comments on this? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Disabling triggers / constraints
Doing it with alter table seems to imply that the change is permanent (eg, the table loses checking), whereas that is most certainly not what is wanted. With a SET variable it lasts only for the session, and doesn't have to be reset manually. Ah, then in that case, how about adding to the existing SET CONSTRAINTS command? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PATCHES] About PostgreSQL
Hi I want to download PostgreSQL How it is work? valBG
[PATCHES] Disabling triggers / constraints
After trying to do some custom dumping/restoring, and having to resort
to the awful trick of changing the trigger counts on the catalog (the
stuff pg_dump adds), decided to add a couple little variables to control
disabling constraints and triggers. Added them to guc.c so that they
show up in the SET/SHOW list, and added checks accordingly (always at
top level, to avoid unnecessary function calls and loops). Variables are
'disable_constraints' and 'disable_triggers' and they are false by
default.
I am unsure wether it is right to send such an uncalled-for patch, but
seeing as this is such a simple thing that can be quite useful (large
volumes of data), I thought it could be useful.
(apply with patch -p0 from the root distro directory)
Cheers
- Jorge Pereira
*** src/backend/utils/misc/guc.c.orig 2004-05-19 18:52:08.718580424 +0100
--- src/backend/utils/misc/guc.c2004-05-19 18:50:46.162130912 +0100
***
*** 131,136
--- 131,140
int log_min_duration_statement = -1;
+ /* Control disabling of triggers (BS,AS,BR and AR) and constraints (useful on mass
insert from dumps)*/
+ bool disable_constraints;
+ bool disable_triggers;
+
/*
* These variables are all dummies that don't do anything, except in some
*** src/backend/executor/execMain.c.orig2004-05-19 18:53:14.251617888 +0100
--- src/backend/executor/execMain.c 2004-05-19 18:53:19.501819736 +0100
***
*** 90,95
--- 90,99
evalPlanQual *priorepq);
static void EvalPlanQualStop(evalPlanQual *epq);
+
+ extern bool disable_constraints;
+ extern bool disable_triggers;
+
/* end of local decls */
***
*** 1063,1068
--- 1067,1073
/*
* Process BEFORE EACH STATEMENT triggers
*/
+ if ( !disable_triggers )
switch (operation)
{
case CMD_UPDATE:
***
*** 1281,1286
--- 1286,1292
/*
* Process AFTER EACH STATEMENT triggers
*/
+ if ( !disable_triggers )
switch (operation)
{
case CMD_UPDATE:
***
*** 1379,1385
resultRelationDesc = resultRelInfo-ri_RelationDesc;
/* BEFORE ROW INSERT Triggers */
! if (resultRelInfo-ri_TrigDesc
resultRelInfo-ri_TrigDesc-n_before_row[TRIGGER_EVENT_INSERT] 0)
{
HeapTuple newtuple;
--- 1385,1391
resultRelationDesc = resultRelInfo-ri_RelationDesc;
/* BEFORE ROW INSERT Triggers */
! if ( !disable_triggers resultRelInfo-ri_TrigDesc
resultRelInfo-ri_TrigDesc-n_before_row[TRIGGER_EVENT_INSERT] 0)
{
HeapTuple newtuple;
***
*** 1405,1411
/*
* Check the constraints of the tuple
*/
! if (resultRelationDesc-rd_att-constr)
ExecConstraints(resultRelInfo, slot, estate);
/*
--- 1411,1417
/*
* Check the constraints of the tuple
*/
! if ( !disable_constraints resultRelationDesc-rd_att-constr )
ExecConstraints(resultRelInfo, slot, estate);
/*
***
*** 1431,1437
ExecInsertIndexTuples(slot, (tuple-t_self), estate, false);
/* AFTER ROW INSERT Triggers */
! ExecARInsertTriggers(estate, resultRelInfo, tuple);
}
/*
--- 1437,1444
ExecInsertIndexTuples(slot, (tuple-t_self), estate, false);
/* AFTER ROW INSERT Triggers */
! if ( !disable_triggers )
! ExecARInsertTriggers(estate, resultRelInfo, tuple);
}
/*
***
*** 1458,1464
resultRelationDesc = resultRelInfo-ri_RelationDesc;
/* BEFORE ROW DELETE Triggers */
! if (resultRelInfo-ri_TrigDesc
resultRelInfo-ri_TrigDesc-n_before_row[TRIGGER_EVENT_DELETE] 0)
{
booldodelete;
--- 1465,1471
resultRelationDesc = resultRelInfo-ri_RelationDesc;
/* BEFORE ROW DELETE Triggers */
! if ( !disable_triggers resultRelInfo-ri_TrigDesc
resultRelInfo-ri_TrigDesc-n_before_row[TRIGGER_EVENT_DELETE] 0)
{
booldodelete;
***
*** 1525,1531
*/
/* AFTER ROW DELETE Triggers */
! ExecARDeleteTriggers(estate, resultRelInfo, tupleid);
}
/*
--- 1532,1539
*/
/* AFTER ROW DELETE Triggers */
! if ( !disable_triggers )
! ExecARDeleteTriggers(estate, resultRelInfo, tupleid);
}
/*
