Обсуждение: timetravel.c
Hi! I am a new person in list. I am dont good speak english, sorry. I rewritten the contrib/spy/timetravel.c, because: on original version of postgresql 7.3.2-7.3.3: the UPDATE not work on timetravel.example if I added >create unique index tttest_idx on tttest (price_id,price_off); >update tttest set price_val = 30 where price_id = 3; ERROR: Cannot insert a duplicate key into unique index tttest_idx And UPDATE not work on table tttest after >alter table tttest add column q1 text; >alter table tttest add column q2 int; >alter table tttest drop column q1; >update tttest set price_val = 30 where price_id = 3; ERROR: Parameter '$5' is out of range (because the trigger is added the deleted q1 column for insert statement) And I add a new optional feature: my new timetravel have +3 optional parameters: inserter_user, updater_user, deleter_user. And I add a new function: get_timetravel for get timetravel status without change it. A big difference: the old version on UPDATE changed oid on active ('infinity') record, the new version UPDATE keep oid, and the overdued record have a new oid. What am I doing with this? Send file with attachement to this list? Sorry for my english Zoltan
Great. Please send over the file as an attachment and I will see that it is added, or even better, use diff -c against the 7.3.2 version and your new version so we can see the changes. --------------------------------------------------------------------------- B�jthe Zolt�n wrote: > Hi! > > I am a new person in list. I am dont good speak english, sorry. > > I rewritten the contrib/spy/timetravel.c, because: > > on original version of postgresql 7.3.2-7.3.3: > > the UPDATE not work on timetravel.example if I added > >create unique index tttest_idx on tttest (price_id,price_off); > >update tttest set price_val = 30 where price_id = 3; > ERROR: Cannot insert a duplicate key into unique index tttest_idx > > And UPDATE not work on table tttest after > >alter table tttest add column q1 text; > >alter table tttest add column q2 int; > >alter table tttest drop column q1; > >update tttest set price_val = 30 where price_id = 3; > ERROR: Parameter '$5' is out of range > (because the trigger is added the deleted q1 column for insert statement) > > And I add a new optional feature: my new timetravel have +3 optional > parameters: > inserter_user, updater_user, deleter_user. > > And I add a new function: get_timetravel for get timetravel status > without change it. > > A big difference: > the old version on UPDATE changed oid on active ('infinity') record, > the new version UPDATE keep oid, and the overdued record have a new oid. > > What am I doing with this? Send file with attachement to this list? > > Sorry for my english > > Zoltan > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian írta: >Great. Please send over the file as an attachment and I will see that >it is added, or even better, use diff -c against the 7.3.2 version and >your new version so we can see the changes. > >--------------------------------------------------------------------------- > > > Hi! Here is 4 file in tgz: the new timetravel.c, new timetravel.README (cut from spi/README and modified), modified timetravel.sql.in and modified timetravel.example. Features: - optionally 3 parameter for insert/update/delete user name - work with CREATE UNIQUE INDEX ixxx on table xxx (unique_field,time_off); (the original version was work with unique index on 6.5.0-6.5.3, and not work on 7.3.2,7.3.3) (before 6.5.0 and between 6.5.3 and 7.3.2 I dont know) - get_timetravel(tablename) function for check timetravel-status. - timetravel trigger not change oid of the active record. (it is not a good feature, because the old version is automatice prevent the paralel update with "where oid=nnn")
Вложения
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. --------------------------------------------------------------------------- B�jthe Zolt�n wrote: > Bruce Momjian ?rta: > > >Great. Please send over the file as an attachment and I will see that > >it is added, or even better, use diff -c against the 7.3.2 version and > >your new version so we can see the changes. > > > >--------------------------------------------------------------------------- > > > > > > > Hi! > > Here is 4 file in tgz: > the new timetravel.c, > new timetravel.README (cut from spi/README and modified), > modified timetravel.sql.in > and modified timetravel.example. > > Features: > - optionally 3 parameter for insert/update/delete user name > > - work with CREATE UNIQUE INDEX ixxx on table xxx (unique_field,time_off); > (the original version was work with unique index on 6.5.0-6.5.3, > and not work on 7.3.2,7.3.3) > (before 6.5.0 and between 6.5.3 and 7.3.2 I dont know) > > - get_timetravel(tablename) function for check timetravel-status. > > - timetravel trigger not change oid of the active record. (it is not a > good feature, because the old version is automatice prevent the paralel > update with "where oid=nnn") > [ application/x-gtar is not supported, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- B�jthe Zolt�n wrote: > Bruce Momjian ?rta: > > >Great. Please send over the file as an attachment and I will see that > >it is added, or even better, use diff -c against the 7.3.2 version and > >your new version so we can see the changes. > > > >--------------------------------------------------------------------------- > > > > > > > Hi! > > Here is 4 file in tgz: > the new timetravel.c, > new timetravel.README (cut from spi/README and modified), > modified timetravel.sql.in > and modified timetravel.example. > > Features: > - optionally 3 parameter for insert/update/delete user name > > - work with CREATE UNIQUE INDEX ixxx on table xxx (unique_field,time_off); > (the original version was work with unique index on 6.5.0-6.5.3, > and not work on 7.3.2,7.3.3) > (before 6.5.0 and between 6.5.3 and 7.3.2 I dont know) > > - get_timetravel(tablename) function for check timetravel-status. > > - timetravel trigger not change oid of the active record. (it is not a > good feature, because the old version is automatice prevent the paralel > update with "where oid=nnn") > [ application/x-gtar is not supported, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian írta: >Patch applied. Thanks. > >--------------------------------------------------------------------------- > > Hi! on my timetravel.c I find a bug: after the ALTER TABLE mytable drop column last_column_of_table; the timetravel trigger say on UPDATE/DELETE: ERROR: parser: parse error at end of input Here is the patch for this bug Böjthe Zoltán --- timetravel.c.prev 2003-06-27 19:56:40.000000000 +0200 +++ timetravel.c 2003-09-02 14:53:21.000000000 +0200 @@ -306,7 +306,7 @@ void *pplan; Oid *ctypes; char sql[8192]; - int j; + char separ=' '; /* allocate ctypes for preparation */ ctypes = (Oid *) palloc(natts * sizeof(Oid)); @@ -319,11 +319,12 @@ { ctypes[i - 1] = SPI_gettypeid(tupdesc, i); if(!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */ - snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d%s", - i, (i < natts) ? ", " : ")" ); -// snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d /* %d */ %s", -// i, ctypes[i-1], (i < natts) ? ", " : ")" ); + { + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ,i); + separ = ','; + } } + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")"); // elog(NOTICE, "timetravel (%s) update: sql: %s", relname, sql);
Bruce Momjian írta: >Patch applied. Thanks. > >--------------------------------------------------------------------------- > > Hi! on my timetravel.c I find a bug: after the ALTER TABLE mytable drop column last_column_of_table; the timetravel trigger say on UPDATE/DELETE: ERROR: parser: parse error at end of input Here is the patch for this bug Böjthe Zoltán --- timetravel.c.prev 2003-06-27 19:56:40.000000000 +0200 +++ timetravel.c 2003-09-02 14:53:21.000000000 +0200 @@ -306,7 +306,7 @@ void *pplan; Oid *ctypes; char sql[8192]; - int j; + char separ=' '; /* allocate ctypes for preparation */ ctypes = (Oid *) palloc(natts * sizeof(Oid)); @@ -319,11 +319,12 @@ { ctypes[i - 1] = SPI_gettypeid(tupdesc, i); if(!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */ - snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d%s", - i, (i < natts) ? ", " : ")" ); -// snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d /* %d */ %s", -// i, ctypes[i-1], (i < natts) ? ", " : ")" ); + { + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ,i); + separ = ','; + } } + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")"); // elog(NOTICE, "timetravel (%s) update: sql: %s", relname, sql);
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. --------------------------------------------------------------------------- B�jthe Zolt�n wrote: > Bruce Momjian ?rta: > > >Patch applied. Thanks. > > > >--------------------------------------------------------------------------- > > > > > Hi! > > on my timetravel.c I find a bug: after the > ALTER TABLE mytable drop column last_column_of_table; > > the timetravel trigger say on UPDATE/DELETE: > > ERROR: parser: parse error at end of input > > > Here is the patch for this bug > > B?jthe Zolt?n > --- timetravel.c.prev 2003-06-27 19:56:40.000000000 +0200 > +++ timetravel.c 2003-09-02 14:53:21.000000000 +0200 > @@ -306,7 +306,7 @@ > void *pplan; > Oid *ctypes; > char sql[8192]; > - int j; > + char separ=' '; > > /* allocate ctypes for preparation */ > ctypes = (Oid *) palloc(natts * sizeof(Oid)); > @@ -319,11 +319,12 @@ > { > ctypes[i - 1] = SPI_gettypeid(tupdesc, i); > if(!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */ > - snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d%s", > - i, (i < natts) ? ", " : ")" ); > -// snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d /* %d */ %s", > -// i, ctypes[i-1], (i < natts) ? ", " : ")" ); > + { > + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ,i); > + separ = ','; > + } > } > + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")"); > > // elog(NOTICE, "timetravel (%s) update: sql: %s", relname, sql); > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
I had to modify your patch sligthly to fit more recent code changes --- updated version attached and applied. --------------------------------------------------------------------------- B�jthe Zolt�n wrote: > Bruce Momjian ?rta: > > >Patch applied. Thanks. > > > >--------------------------------------------------------------------------- > > > > > Hi! > > on my timetravel.c I find a bug: after the > ALTER TABLE mytable drop column last_column_of_table; > > the timetravel trigger say on UPDATE/DELETE: > > ERROR: parser: parse error at end of input > > > Here is the patch for this bug > > B?jthe Zolt?n > --- timetravel.c.prev 2003-06-27 19:56:40.000000000 +0200 > +++ timetravel.c 2003-09-02 14:53:21.000000000 +0200 > @@ -306,7 +306,7 @@ > void *pplan; > Oid *ctypes; > char sql[8192]; > - int j; > + char separ=' '; > > /* allocate ctypes for preparation */ > ctypes = (Oid *) palloc(natts * sizeof(Oid)); > @@ -319,11 +319,12 @@ > { > ctypes[i - 1] = SPI_gettypeid(tupdesc, i); > if(!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */ > - snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d%s", > - i, (i < natts) ? ", " : ")" ); > -// snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d /* %d */ %s", > -// i, ctypes[i-1], (i < natts) ? ", " : ")" ); > + { > + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ,i); > + separ = ','; > + } > } > + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")"); > > // elog(NOTICE, "timetravel (%s) update: sql: %s", relname, sql); > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: contrib/spi/timetravel.c =================================================================== RCS file: /cvsroot/pgsql-server/contrib/spi/timetravel.c,v retrieving revision 1.18 diff -c -c -r1.18 timetravel.c *** contrib/spi/timetravel.c 4 Aug 2003 00:43:11 -0000 1.18 --- contrib/spi/timetravel.c 11 Sep 2003 17:20:08 -0000 *************** *** 309,314 **** --- 309,315 ---- void *pplan; Oid *ctypes; char sql[8192]; + char separ=' '; /* allocate ctypes for preparation */ ctypes = (Oid *) palloc(natts * sizeof(Oid)); *************** *** 321,333 **** { ctypes[i - 1] = SPI_gettypeid(tupdesc, i); if (!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */ ! snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d%s", ! i, (i < natts) ? ", " : ")"); ! #if 0 ! snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "$%d /* %d */ %s", ! i, ctypes[i - 1], (i < natts) ? ", " : ")"); ! #endif } elog(DEBUG4, "timetravel (%s) update: sql: %s", relname, sql); --- 322,333 ---- { ctypes[i - 1] = SPI_gettypeid(tupdesc, i); if (!(tupdesc->attrs[i - 1]->attisdropped)) /* skip dropped columns */ ! { ! snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%c$%d", separ,i); ! separ = ','; ! } } + snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ")"); elog(DEBUG4, "timetravel (%s) update: sql: %s", relname, sql);