Horton

Thread

  1. XID Data Types

    Ericson Smith <eric@did-it.com> — 2004-05-03T17:38:45Z

    Hello everyone,

Take the query below:

select *,xmin from settings where xmin > '187167023'::xid limit 5;

Although the docs say this is a 32 bit datatype, I have casted the 
integer '187167023' also to an xid type, but it seems that there is no 
operator to do a comparison, since I get this error:

didit=# select *,xmin from settings where xmin > '187167023'::xid limit 5;
ERROR:  operator does not exist: xid > xid

Any pointers or suggestions?

- Ericson

  2. Re: XID Data Types

    Manfred Koizar <mkoi-pg@aon.at> — 2004-05-03T18:36:33Z

    On Mon, 03 May 2004 13:38:45 -0400, Ericson Smith <eric@did-it.com>
wrote:
>didit=# select *,xmin from settings where xmin > '187167023'::xid limit 5;
>ERROR:  operator does not exist: xid > xid

There might be better solutions, but when I ran into the same problem,
the age(xid) function helped at lot.

  3. Re: XID Data Types

    Ericson Smith <eric@did-it.com> — 2004-05-03T21:31:51Z

    I've been reading about the CREATE OPERATOR command.

Apparently there is an operator to test equality between an xmin type 
and an integer type, how do it go about creating another type to make a 
 > or < comparison ?

The documents are pretty straightforward about creating other types, but 
I was not able to find much information about the xid type. In pg_proc, 
I found that there are the following comparators:

xideqint4
xideq

Which would make a equal comparison between an xid = int4, or xid=xid. 
This seems to work ok. My goal is to find a decent way of syncing 
records with an external cache. The xmin field that exists within every 
table is updated whenever a transaction is successful on record(s). I 
have a process that will run, and look at the last xmin value (I stored 
in another table), and grab records that have an xmin greater than  the 
last stored value. We think that this is a better way to sync data than 
the old way of using triggers that updated a sync table on inserts and 
deletes. Only certain tables need to be sync'd with our external caches.

Any suggestions if this is the right way to go, or is there another way 
that this particular problem could be solved?

Any suggestions would be really appreciated.

Regards
- Ericson Smith


Manfred Koizar wrote:

>On Mon, 03 May 2004 13:38:45 -0400, Ericson Smith <eric@did-it.com>
>wrote:
>  
>
>>didit=# select *,xmin from settings where xmin > '187167023'::xid limit 5;
>>ERROR:  operator does not exist: xid > xid
>>    
>>
>
>There might be better solutions, but when I ran into the same problem,
>the age(xid) function helped at lot.
>
>
>  
>

  4. Re: XID Data Types

    Jan Wieck <janwieck@yahoo.com> — 2004-05-06T14:31:22Z

    Manfred Koizar wrote:
> On Mon, 03 May 2004 13:38:45 -0400, Ericson Smith <eric@did-it.com>
> wrote:
>>didit=# select *,xmin from settings where xmin > '187167023'::xid limit 5;
>>ERROR:  operator does not exist: xid > xid
> 
> There might be better solutions, but when I ran into the same problem,
> the age(xid) function helped at lot.

Slony contains an xxid data type that follows exactly the xid semantics. 
It comes with a full operator class so that you can even put indexes on 
xxid type columns. Shouldn't be too hard to define those operators for 
the builtin xid type instead.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

  5. Re: XID Data Types

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-06T15:08:56Z

    Jan Wieck wrote:
> Manfred Koizar wrote:
> > On Mon, 03 May 2004 13:38:45 -0400, Ericson Smith <eric@did-it.com>
> > wrote:
> >>didit=# select *,xmin from settings where xmin > '187167023'::xid limit 5;
> >>ERROR:  operator does not exist: xid > xid
> > 
> > There might be better solutions, but when I ran into the same problem,
> > the age(xid) function helped at lot.
> 
> Slony contains an xxid data type that follows exactly the xid semantics. 
> It comes with a full operator class so that you can even put indexes on 
> xxid type columns. Shouldn't be too hard to define those operators for 
> the builtin xid type instead.

Should we just get that into our CVS so Slony doesn't have to distribute
its own?

-- 
  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

  6. Re: XID Data Types

    Jan Wieck <janwieck@yahoo.com> — 2004-05-07T03:12:02Z

    Bruce Momjian wrote:
> Jan Wieck wrote:
>> Manfred Koizar wrote:
>> > On Mon, 03 May 2004 13:38:45 -0400, Ericson Smith <eric@did-it.com>
>> > wrote:
>> >>didit=# select *,xmin from settings where xmin > '187167023'::xid limit 5;
>> >>ERROR:  operator does not exist: xid > xid
>> > 
>> > There might be better solutions, but when I ran into the same problem,
>> > the age(xid) function helped at lot.
>> 
>> Slony contains an xxid data type that follows exactly the xid semantics. 
>> It comes with a full operator class so that you can even put indexes on 
>> xxid type columns. Shouldn't be too hard to define those operators for 
>> the builtin xid type instead.
> 
> Should we just get that into our CVS so Slony doesn't have to distribute
> its own?
> 

It has to anyway, at least as long as it is supposed to be a version 
upgrade aid that supports replicating from 7.3.x or 7.4.x to a newer 
version so that the upgrade downtime reduces to a few seconds for a 
master->slave switchover.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

  7. Re: XID Data Types

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-07T03:39:42Z

    Jan Wieck <JanWieck@Yahoo.com> writes:
> Bruce Momjian wrote:
>> Should we just get that into our CVS so Slony doesn't have to distribute
>> its own?

> It has to anyway, at least as long as it is supposed to be a version 
> upgrade aid that supports replicating from 7.3.x or 7.4.x

No doubt ... but I agree that we *should* push full comparison support
for type XID into CVS.

			regards, tom lane

  8. Re: XID Data Types

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-05-07T03:58:10Z

    Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> > Bruce Momjian wrote:
> >> Should we just get that into our CVS so Slony doesn't have to distribute
> >> its own?
> 
> > It has to anyway, at least as long as it is supposed to be a version 
> > upgrade aid that supports replicating from 7.3.x or 7.4.x
> 
> No doubt ... but I agree that we *should* push full comparison support
> for type XID into CVS.

Yes, let's at least match it so we can have testing of the code in both
places, and prevent the need to load them in if the version is new
enough.

-- 
  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