xlog location arithmetic

On Tue, Dec 6, 2011 at 05:19, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

Hi,

A while ago when blogging about WAL [1], I noticed a function to deal with
xlog location arithmetic is wanted. I remembered Depez [2] mentioning it and
after some questions during trainings and conferences I decided to translate
my shell script function in C.

The attached patch implements the function pg_xlog_location_diff (bikeshed
colors are welcome). It calculates the difference between two given
transaction log locations. Now that we have pg_stat_replication view, it will
be easy to get the lag just passing columns as parameters. Also, the
monitoring tools could take advantage of it instead of relying on a fragile
routine to get the lag.

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

Also, why do you use DirectFunctionCall to do the simple math, and not
just do the math right there in the function?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander <magnus@hagander.net> wrote:

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

I went so far as to put together an lsn data type. I didn't actually
get all that far with it, which is why I haven't posted it sooner, but
here's what I came up with. It's missing indexing support and stuff,
but that could be added if people like the approach. It solves this
problem by implementing -(lsn,lsn) => numeric (not int8, that can
overflow since it is not unsigned), which allows an lsn => numeric
conversion by just subtracting '0/0'::lsn.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 06-12-2011 07:14, Magnus Hagander wrote:

On Tue, Dec 6, 2011 at 05:19, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

Hi,

A while ago when blogging about WAL [1], I noticed a function to deal with
xlog location arithmetic is wanted. I remembered Depez [2] mentioning it and
after some questions during trainings and conferences I decided to translate
my shell script function in C.

The attached patch implements the function pg_xlog_location_diff (bikeshed
colors are welcome). It calculates the difference between two given
transaction log locations. Now that we have pg_stat_replication view, it will
be easy to get the lag just passing columns as parameters. Also, the
monitoring tools could take advantage of it instead of relying on a fragile
routine to get the lag.

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

The only advantage is that you don't expose the arithmetic, e.g., user doesn't
need to know the xlog internals (like I described in a recent blog post). If
one day we consider changes in xlog arithmetic (for example, XLogFileSize), we
don't need to worry too much about external tools.

Also, why do you use DirectFunctionCall to do the simple math, and not
just do the math right there in the function?

I use it because I don't want to duplicate the overflow code.

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

On 06-12-2011 13:11, Robert Haas wrote:

On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander <magnus@hagander.net> wrote:

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

I went so far as to put together an lsn data type. I didn't actually
get all that far with it, which is why I haven't posted it sooner, but
here's what I came up with. It's missing indexing support and stuff,
but that could be added if people like the approach. It solves this
problem by implementing -(lsn,lsn) => numeric (not int8, that can
overflow since it is not unsigned), which allows an lsn => numeric
conversion by just subtracting '0/0'::lsn.

Interesting approach. I don't want to go that far. If so, you want to change
all of those functions that deal with LSNs and add some implicit conversion
between text and lsn data types (for backward compatibility). As of int8, I'm
not aware of any modern plataform that int8 is not 64 bits. I'm not against
numeric use; I'm just saying that int8 is sufficient.

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

On Tue, Dec 6, 2011 at 1:00 PM, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

On 06-12-2011 13:11, Robert Haas wrote:

On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander <magnus@hagander.net> wrote:

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

I went so far as to put together an lsn data type.  I didn't actually
get all that far with it, which is why I haven't posted it sooner, but
here's what I came up with.  It's missing indexing support and stuff,
but that could be added if people like the approach.  It solves this
problem by implementing -(lsn,lsn) => numeric (not int8, that can
overflow since it is not unsigned), which allows an lsn => numeric
conversion by just subtracting '0/0'::lsn.

Interesting approach. I don't want to go that far. If so, you want to change
all of those functions that deal with LSNs and add some implicit conversion
between text and lsn data types (for backward compatibility). As of int8, I'm
not aware of any modern plataform that int8 is not 64 bits. I'm not against
numeric use; I'm just saying that int8 is sufficient.

The point isn't that int8 might not be 64 bits - of course it has to
be 64 bits; that's why it's called int8 i.e. 8 bytes. The point is
that a large enough LSN, represented as an int8, will come out as a
negative values. int8 can only represent 2^63 *non-negative* values,
because one bit is reserved for sign.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Dec 6, 2011, at 12:06 PM, Robert Haas wrote:

On Tue, Dec 6, 2011 at 1:00 PM, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

On 06-12-2011 13:11, Robert Haas wrote:

On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander <magnus@hagander.net> wrote:

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

I went so far as to put together an lsn data type. I didn't actually
get all that far with it, which is why I haven't posted it sooner, but
here's what I came up with. It's missing indexing support and stuff,
but that could be added if people like the approach. It solves this
problem by implementing -(lsn,lsn) => numeric (not int8, that can
overflow since it is not unsigned), which allows an lsn => numeric
conversion by just subtracting '0/0'::lsn.

Interesting approach. I don't want to go that far. If so, you want to change
all of those functions that deal with LSNs and add some implicit conversion
between text and lsn data types (for backward compatibility). As of int8, I'm
not aware of any modern plataform that int8 is not 64 bits. I'm not against
numeric use; I'm just saying that int8 is sufficient.

The point isn't that int8 might not be 64 bits - of course it has to
be 64 bits; that's why it's called int8 i.e. 8 bytes. The point is
that a large enough LSN, represented as an int8, will come out as a
negative values. int8 can only represent 2^63 *non-negative* values,
because one bit is reserved for sign.

I've often wondered about adding uint2/4/8... I suspect it's actually pretty uncommon for people to put negative numbers into int fields, since one of their biggest uses seems to be surrogate keys.

I realize that this opens a can of worms with casting, but perhaps that can be kept under control by not doing any implicit casting between int and uint... that just means that we'd have to be smart about casting from unknown, but hopefully that's doable since we already have a similar concern with casting unknown to int2/4/8 vs numeric?
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

On Tue, Dec 13, 2011 at 12:48 PM, Jim Nasby <jim@nasby.net> wrote:

I've often wondered about adding uint2/4/8... I suspect it's actually pretty uncommon for people to put negative numbers into int fields, since one of their biggest uses seems to be surrogate keys.

I realize that this opens a can of worms with casting, but perhaps that can be kept under control by not doing any implicit casting between int and uint... that just means that we'd have to be smart about casting from unknown, but hopefully that's doable since we already have a similar concern with casting unknown to int2/4/8 vs numeric?

I've wondered about it too, but it seems like too large a can of worms
to open just to address this case. Returning the value as numeric is
hardly a disaster; the user can always downcast to int8 if they really
want, and as long as it's < 2^63 (which in practice it virtually
always will be) it will work. It's not clear what the point of this
is since for typical values numeric is going to take up less storage
anyway (e.g. 1000001 is 7 bytes on disk as a numeric), not to mention
that it only requires 4-byte alignment rather than 8-byte alignment,
and probably no one does enough arithmetic with LSN values for any
speed penalty to matter even slightly, but it should work.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Dec 6, 2011 at 19:06, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Dec 6, 2011 at 1:00 PM, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

On 06-12-2011 13:11, Robert Haas wrote:

On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander <magnus@hagander.net> wrote:

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

I went so far as to put together an lsn data type.  I didn't actually
get all that far with it, which is why I haven't posted it sooner, but
here's what I came up with.  It's missing indexing support and stuff,
but that could be added if people like the approach.  It solves this
problem by implementing -(lsn,lsn) => numeric (not int8, that can
overflow since it is not unsigned), which allows an lsn => numeric
conversion by just subtracting '0/0'::lsn.

Interesting approach. I don't want to go that far. If so, you want to change
all of those functions that deal with LSNs and add some implicit conversion
between text and lsn data types (for backward compatibility). As of int8, I'm

As long as you have the conversion, you don't really need to change
them, do you? It might be nice in some ways, but this is still a
pretty internal operation, so I don't see it as critical.

not aware of any modern plataform that int8 is not 64 bits. I'm not against
numeric use; I'm just saying that int8 is sufficient.

The point isn't that int8 might not be 64 bits - of course it has to
be 64 bits; that's why it's called int8 i.e. 8 bytes.  The point is
that a large enough LSN, represented as an int8, will come out as a
negative values.  int8 can only represent 2^63 *non-negative* values,
because one bit is reserved for sign.

Doing it in numeric should be perfectly fine. The only real reason to
pick int8 over in this context would be performance, but it's not like
this is something that's going to be called in really performance
critical paths...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 20-12-2011 07:27, Magnus Hagander wrote:

On Tue, Dec 6, 2011 at 19:06, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Dec 6, 2011 at 1:00 PM, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

On 06-12-2011 13:11, Robert Haas wrote:

On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander <magnus@hagander.net> wrote:

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

I went so far as to put together an lsn data type. I didn't actually
get all that far with it, which is why I haven't posted it sooner, but
here's what I came up with. It's missing indexing support and stuff,
but that could be added if people like the approach. It solves this
problem by implementing -(lsn,lsn) => numeric (not int8, that can
overflow since it is not unsigned), which allows an lsn => numeric
conversion by just subtracting '0/0'::lsn.

Interesting approach. I don't want to go that far. If so, you want to change
all of those functions that deal with LSNs and add some implicit conversion
between text and lsn data types (for backward compatibility). As of int8, I'm

As long as you have the conversion, you don't really need to change
them, do you? It might be nice in some ways, but this is still a
pretty internal operation, so I don't see it as critical.

For correctness, yes.

At this point, my question is: do we want to support the lsn data type idea or
a basic function that implements the difference between LSNs?

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

On Tue, Dec 20, 2011 at 14:08, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

On 20-12-2011 07:27, Magnus Hagander wrote:

On Tue, Dec 6, 2011 at 19:06, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Dec 6, 2011 at 1:00 PM, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

On 06-12-2011 13:11, Robert Haas wrote:

On Tue, Dec 6, 2011 at 5:14 AM, Magnus Hagander <magnus@hagander.net> wrote:

I've been considering similar things, as you can find in the archives,
but what I was thinking of was converting the number to just a plain
bigint, then letting the user apply whatever arithmetic wanted at the
SQL level. I never got around to acutally coding it, though. It could
easily be extracted from your patch of course - and I think that's a
more flexible approach. Is there some advantage to your method that
I'm missing?

I went so far as to put together an lsn data type.  I didn't actually
get all that far with it, which is why I haven't posted it sooner, but
here's what I came up with.  It's missing indexing support and stuff,
but that could be added if people like the approach.  It solves this
problem by implementing -(lsn,lsn) => numeric (not int8, that can
overflow since it is not unsigned), which allows an lsn => numeric
conversion by just subtracting '0/0'::lsn.

Interesting approach. I don't want to go that far. If so, you want to change
all of those functions that deal with LSNs and add some implicit conversion
between text and lsn data types (for backward compatibility). As of int8, I'm

As long as you have the conversion, you don't really need to change
them, do you? It might be nice in some ways, but this is still a
pretty internal operation, so I don't see it as critical.

For correctness, yes.

At this point, my question is: do we want to support the lsn data type idea or
a basic function that implements the difference between LSNs?

Personally I think a function is enough - it solves the only case that
I've actually seen. But a datatype would be a more complete solution,
of course - but it seems a bit of an overkill to me. Not really sure
which way we should go - I was hoping somebody else would comment as
well..

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:

At this point, my question is: do we want to support the lsn data type idea or
a basic function that implements the difference between LSNs?

Personally I think a function is enough - it solves the only case that
I've actually seen. But a datatype would be a more complete solution,
of course - but it seems a bit of an overkill to me. Not really sure
which way we should go - I was hoping somebody else would comment as
well..

I too think a datatype is overkill, if we're only planning on providing
one function. Just emit the values as numeric and have done.

regards, tom lane

On Fri, Dec 23, 2011 at 10:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I too think a datatype is overkill, if we're only planning on providing
one function.

Are there any other functions we ought to provide?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12/23/2011 10:05 AM, Tom Lane wrote:

Magnus Hagander<magnus@hagander.net> writes:

At this point, my question is: do we want to support the lsn data type idea or
a basic function that implements the difference between LSNs?

Personally I think a function is enough - it solves the only case that
I've actually seen. But a datatype would be a more complete solution,
of course - but it seems a bit of an overkill to me. Not really sure
which way we should go - I was hoping somebody else would comment as
well..

I too think a datatype is overkill, if we're only planning on providing
one function. Just emit the values as numeric and have done.

+1.

cheers

andrew

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Dec 23, 2011 at 10:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I too think a datatype is overkill, if we're only planning on providing
one function.

Are there any other functions we ought to provide?

Even if there are several, what exact advantage does a datatype offer
over representing LSN values as numerics? It seems to me to be adding
complication and extra code (I/O converters at least) for very little
gain.

regards, tom lane

On Fri, Dec 23, 2011 at 10:18 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Dec 23, 2011 at 10:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I too think a datatype is overkill, if we're only planning on providing
one function.

Are there any other functions we ought to provide?

Even if there are several, what exact advantage does a datatype offer
over representing LSN values as numerics?  It seems to me to be adding
complication and extra code (I/O converters at least) for very little
gain.

I guess I'm just constitutionally averse to labeling things as "text"
when they really aren't. I do it all the time in Perl, of course, but
in PostgreSQL we have strong data typing, and it seems like we might
as well use it.

Also, we've occasionally talked (in the light of Pavan's single-pass
vacuum patch, for example) about needing to store LSNs in system
catalogs; and we're certainly not going to want to do that as text.
I'll admit that it's not 100% clear that anything like this will ever
happen, though, so maybe it's premature to worry about it.

I can see I'm in the minority on this one, though...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Dec 23, 2011 at 10:18 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Even if there are several, what exact advantage does a datatype offer
over representing LSN values as numerics? It seems to me to be adding
complication and extra code (I/O converters at least) for very little
gain.

I guess I'm just constitutionally averse to labeling things as "text"
when they really aren't.

Er ... text? I thought the proposal was to use numeric.

regards, tom lane

On Fri, Dec 23, 2011 at 10:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Dec 23, 2011 at 10:18 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Even if there are several, what exact advantage does a datatype offer
over representing LSN values as numerics?  It seems to me to be adding
complication and extra code (I/O converters at least) for very little
gain.

I guess I'm just constitutionally averse to labeling things as "text"
when they really aren't.

Er ... text?  I thought the proposal was to use numeric.

The proposal is to make a function that takes a text argument (which
is really an LSN, but we choose to represent it as text) and returns
numeric.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Dec 6, 2011 at 1:19 PM, Euler Taveira de Oliveira
<euler@timbira.com> wrote:

Hi,

A while ago when blogging about WAL [1], I noticed a function to deal with
xlog location arithmetic is wanted. I remembered Depez [2] mentioning it and
after some questions during trainings and conferences I decided to translate
my shell script function in C.

The attached patch implements the function pg_xlog_location_diff (bikeshed
colors are welcome). It calculates the difference between two given
transaction log locations. Now that we have pg_stat_replication view, it will
be easy to get the lag just passing columns as parameters. Also, the
monitoring tools could take advantage of it instead of relying on a fragile
routine to get the lag.

I noticed that pg_xlogfile_name* functions does not sanity check the xrecoff
boundaries but that is material for another patch.

[1] http://eulerto.blogspot.com/2011/11/understanding-wal-nomenclature.html
[2]
http://www.depesz.com/index.php/2011/01/24/waiting-for-9-1-pg_stat_replication/

I think that this function is very useful. Can you add the patch into
CommitFest 2012-1 ?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On 14-01-2012 11:06, Fujii Masao wrote:

I think that this function is very useful. Can you add the patch into
CommitFest 2012-1 ?

Sure. But I must adjust the patch based on the thread comments (basically,
numeric output). I have a new patch but need to test it before submitting it.
I'll post this weekend.

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento