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.

---------------------------------------------------------------------------

Bjthe Zoltn 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(at)candle(dot)pha(dot)pa(dot)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.

---------------------------------------------------------------------------

Bjthe Zoltn 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(at)candle(dot)pha(dot)pa(dot)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.

---------------------------------------------------------------------------

Bjthe Zoltn 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(at)candle(dot)pha(dot)pa(dot)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

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

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.

---------------------------------------------------------------------------

Bjthe Zoltn 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(at)candle(dot)pha(dot)pa(dot)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.

---------------------------------------------------------------------------

Bjthe Zoltn 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073