Обсуждение: 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);