Re: Simple row serialization?

Hi,

I'd like to implement some simple data logging via triggers on a small
number of infrequently updated tables and I'm wondering if there are
some helpful functions, plugins or idioms that would serialize a row
(received for example in a AFTER INSERT trigger) into a string that I'd
store in the log table. There's a limited number of field types
involved: varchars, integers and booleans. I'm not looking for anything
fancy, comma-separated string result will be just fine; Even better,
something like a dictionary ("field_name":"field_value",...) would be
nice. The reason for trying to do it this way is that I don't want to
create separate log tables for every table I wish to log.

Hello,

use plperl.

PostgreSQL hasn't simple tool for it. Maybe this link will be usefull for you
http://www.ciselant.de/projects/pg_ci_diff/

Pavel

On 26/01/2008, Ivan Voras <ivoras(at)freebsd(dot)org> wrote:
> Hi,
>
> I'd like to implement some simple data logging via triggers on a small
> number of infrequently updated tables and I'm wondering if there are
> some helpful functions, plugins or idioms that would serialize a row
> (received for example in a AFTER INSERT trigger) into a string that I'd
> store in the log table. There's a limited number of field types
> involved: varchars, integers and booleans. I'm not looking for anything
> fancy, comma-separated string result will be just fine; Even better,
> something like a dictionary ("field_name":"field_value",...) would be
> nice. The reason for trying to do it this way is that I don't want to
> create separate log tables for every table I wish to log.
>
>
>
>

Ivan Voras wrote:
> Hi,
>
> I'd like to implement some simple data logging via triggers on a small
> number of infrequently updated tables and I'm wondering if there are
> some helpful functions, plugins or idioms that would serialize a row
> (received for example in a AFTER INSERT trigger) into a string that
> I'd store in the log table. There's a limited number of field types
> involved: varchars, integers and booleans. I'm not looking for
> anything fancy, comma-separated string result will be just fine; Even
> better, something like a dictionary ("field_name":"field_value",...)
> would be nice. The reason for trying to do it this way is that I don't
> want to create separate log tables for every table I wish to log.

Why not create the audit tables with composite types rather than strings?

create table audit_foo_table as (who text, when timestamptz, old
foo, new foo);

Or you could use an hstore (see contrib).

Or you could possibly use some of the XML support in 8.3 for serialization.

This is a usage question, so it really doesn't belong on -hackers.

cheers

andrew

Pavel Stehule wrote:
> Hello,
>
> use plperl.

I'd like something more light-weight to reduce complexity of deployment.
Something in pgplsql would be ideal. Is there a way to simply iterate
over the fields of a row and retrieve field names and values?

> PostgreSQL hasn't simple tool for it. Maybe this link will be usefull for you
> http://www.ciselant.de/projects/pg_ci_diff/

Thanks, this is very interesting work! It's an overkill for my current
needs but I'll keep it in mind.

Andrew Dunstan wrote:

> Why not create the audit tables with composite types rather than strings?
>
> create table audit_foo_table as (who text, when timestamptz, old foo,
> new foo);

Because this would lead to having a log/shadow/audit table for every
table I wish to log. (or is there an opaque "generic row" data type?
"record" and "any" generate syntax errors).

> Or you could use an hstore (see contrib).

Doesn't seem applicable.

> Or you could possibly use some of the XML support in 8.3 for serialization.

I need this for 8.1 :)

> This is a usage question, so it really doesn't belong on -hackers.

Thank you - I'm reading the list through gmane and I didn't notice its
name "gmane.comp.db.postgresql.devel.general" is incorrect. I'll find a
more suitable list.

Ivan Voras <ivoras(at)freebsd(dot)org> writes:
> Andrew Dunstan wrote:
>> Or you could possibly use some of the XML support in 8.3 for serialization.

> I need this for 8.1 :)

There's an even easier way in 8.3: just cast the rowtype to text.

regression=# select row(1,2,false)::text;
row
---------
(1,2,f)
(1 row)

Although this won't work at the SQL level in 8.1, I think you might be
able to accomplish the equivalent within plpgsql by assigning the
rowtype value to a text variable.

regards, tom lane

On 26/01/2008, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ivan Voras <ivoras(at)freebsd(dot)org> writes:
> > Andrew Dunstan wrote:
> >> Or you could possibly use some of the XML support in 8.3 for serialization.
>
> > I need this for 8.1 :)
>
> There's an even easier way in 8.3: just cast the rowtype to text.
>
> regression=# select row(1,2,false)::text;
> row
> ---------
> (1,2,f)
> (1 row)
>
> Although this won't work at the SQL level in 8.1, I think you might be
> able to accomplish the equivalent within plpgsql by assigning the
> rowtype value to a text variable.
>

you lost names :(. The best of will be support some like dictionary

so select ((row(1,2,3))::sometype)
{{a:1},{b:2},{c:3}}

Regards
Pavel Stehule

> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> On 26/01/2008, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Although this won't work at the SQL level in 8.1, I think you might be
>> able to accomplish the equivalent within plpgsql by assigning the
>> rowtype value to a text variable.

> you lost names :(.

And datatype info too; but AFAICT that's exactly what the OP wants.

regards, tom lane

Tom Lane wrote:
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
>> On 26/01/2008, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Although this won't work at the SQL level in 8.1, I think you might be
>>> able to accomplish the equivalent within plpgsql by assigning the
>>> rowtype value to a text variable.
>
>> you lost names :(.
>
> And datatype info too; but AFAICT that's exactly what the OP wants.

Thanks! Having names would be great, but this is sufficient for now.
(I've tested it and it works!).

Ivan,

have you seen contrib/hstore ? It's perl-like hash data type and can be
ideally suited for you.

Oleg
On Sat, 26 Jan 2008, Ivan Voras wrote:

> Andrew Dunstan wrote:
>
>> Why not create the audit tables with composite types rather than strings?
>>
>> create table audit_foo_table as (who text, when timestamptz, old foo,
>> new foo);
>
> Because this would lead to having a log/shadow/audit table for every table I
> wish to log. (or is there an opaque "generic row" data type? "record" and
> "any" generate syntax errors).
>
>> Or you could use an hstore (see contrib).
>
> Doesn't seem applicable.
>
>> Or you could possibly use some of the XML support in 8.3 for serialization.
>
> I need this for 8.1 :)
>
>> This is a usage question, so it really doesn't belong on -hackers.
>
> Thank you - I'm reading the list through gmane and I didn't notice its name
> "gmane.comp.db.postgresql.devel.general" is incorrect. I'll find a more
> suitable list.
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Jan 26, 2008 at 07:32:35PM +0100, Ivan Voras wrote:
> Andrew Dunstan wrote:
[...]
> >Or you could use an hstore (see contrib).
>
> Doesn't seem applicable.

Have a closer look: it might :-)

regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFHnBcxBcgs9XrR2kYRAv+zAJwPux4ik4JLOHzwyNanUFNkV2yGwACfdZwl
SzA4xZxahgT7d8Z3PyHJwJ4=
=5ECG
-----END PGP SIGNATURE-----

On 27/01/2008, tomas(at)tuxteam(dot)de <tomas(at)tuxteam(dot)de> wrote:

> On Sat, Jan 26, 2008 at 07:32:35PM +0100, Ivan Voras wrote:
> > Andrew Dunstan wrote:
> [...]
> > >Or you could use an hstore (see contrib).
> >
> > Doesn't seem applicable.
>
> Have a closer look: it might :-)

I found these documents on hstore:

http://developer.postgresql.org/pgdocs/postgres/hstore.html
http://www.sai.msu.su/~megera/wiki/Hstore

From them, it seems "hstore" is another datatype (as in:
'a=>b'::hstore), which sounds good (though if it was me I'd have
picked a different name for it, like "dict" or "hashmap" :) ) for
storing both field names and their values, but I don't see a way to
convert a row/record passed to a trigger to a hstore.

Ivan Voras wrote:
> On 27/01/2008, tomas(at)tuxteam(dot)de <tomas(at)tuxteam(dot)de> wrote:
>
>
>> On Sat, Jan 26, 2008 at 07:32:35PM +0100, Ivan Voras wrote:
>>
>>> Andrew Dunstan wrote:
>>>
>> [...]
>>
>>>> Or you could use an hstore (see contrib).
>>>>
>>> Doesn't seem applicable.
>>>
>> Have a closer look: it might :-)
>>
>
> I found these documents on hstore:
>
> http://developer.postgresql.org/pgdocs/postgres/hstore.html
> http://www.sai.msu.su/~megera/wiki/Hstore
>
> >From them, it seems "hstore" is another datatype (as in:
> 'a=>b'::hstore), which sounds good (though if it was me I'd have
> picked a different name for it, like "dict" or "hashmap" :) ) for
> storing both field names and their values, but I don't see a way to
> convert a row/record passed to a trigger to a hstore.
>
>
>

It's trivial to do in a plperl trigger, since it gets the new and old
records as hashes with the field names as keys.

cheers

andrew