Generating a GUID equivalent in PostgreSQL

[MostlyHuman]

I'm updating a PgSQL application that needs to allow moving of full or partial datasets between databases and servers. Using serial values as primary keys is problematic, as the increment values obviously are not synched on different servers, etc.

I've written a simple plpgsql function to generate a strong primary key, without using serial values. It is a poor-man's equivalent of a GUID (globally unique identifier), at least within our own production environment. I wanted to post it here and see what comments anyone might have. I realize that using a table OID may not be the best server ID, but if a strong server method ID is chosen, what problems does anyone foresee if I use this to generate primary keys with the intent to freely move data between servers and databases?

Here's the function:

/*
plpgsql function which generates a primary key with a strong probability of
being unique across multiple servers and databases. Can be
used to generate strong primary keys which can enhance portability
of data, and eliminate reliance on serial indexes (i.e. autoincrement fields)
to maintain uniqueness of primary keys.

The function retrieves the current time of day, using the timeofday() function,
which will return the actual current time of day (CURRENT_TIMESTAMP function
will return only the time when the TRANSACTION begins, not for the insertion
of each row). The OID of a given table is retrived as well. The table OID is
combined with the current datetime converted in Unix epoch and right-padded
with zeros. The resulting key is a 30-char varchar.

The table OID could (and perhaps should) be replaced with another value that is unique
to a given server, and available to this function.

*/

declare
ts timestamp;
tbl record;
id varchar;
pkey varchar;

begin

/* Get the current date and time */
ts := timeofday();

/* Get the OID from a given table, or replace with other server ID */
SELECT * INTO tbl FROM (SELECT oid FROM pg_class WHERE relname = 'tablename') AS foo;

/* Store the server ID value */
id := tbl.oid;

/* Combine server ID, and epoch value from timestamp and right-pad with zeros */
pkey := rpad((id || '.' || EXTRACT(EPOCH FROM ts)),30,'0');

/* Return the key as a varchar
return pkey;

end;

 

[Leland123]

Hi MostlyHuman,

Here are a couple of more ideas:

I would designate one of the servers to provide the GUID. On the designated server I would create a database with a table that had a serial primary key and a timestamp. Each time a GUID was need, I would have the application insert a record into the GUID table and then immediately do a currval() function to return the auto increamented value. I would then use the returned currval as the value assigned when inserting a new primary record in a table. Here is kind of how it might look using perl:


#------------------------------
#!/usr/bin/perl

###### Here is a snippet of code.
###### Notice that when the insert is done, only
###### the time is passed to the table as an
###### insert value, as postgres will auto
###### increment the serial field. To get the
###### incremented value I use the currval()

########################
## Create a reference to the Smglobal class
########################

use Smglobal;
$oMy = Smglobal->new();

#############################################
## Get next invoice number from the invoice sequencer
## by inserting a record into the auto_invoice_no
## table.
###############################################

$theinsert = "INSERT INTO auto_invoice_no (seq_time) VALUES ('now')";

$sth = $oMy->sql_exec($theinsert);

########################################
## Now retrieve the incremented value by using
## the currval function.
##########################################

$the_seq_no = "SELECT currval('auto_invoice_no_invoiceno_seq')";

$sth = $oMy->sql_exec($the_seq_no);

$array_ref = $sth->fetchall_arrayref();
foreach my $row (@$array_ref)
{
@temp = @$row;
}

$new_invoiceno=$temp[0];
#################################################

Here is the sql_exec module that is part of
the Smglobal class. All SQL, whether insert,
update, or delete is passed to this module
for execution. If there is a error, the
postgresql error message and the SQL passed
to the sgl_exec function will be displayed.



########################################################
################### sql_exec #########################
########################################################
sub sql_exec {

my $self = shift;

my ($the_sql) = @_;

$self->{DBH}->quote($the_sql);

if ( $self->{THE_AUTOCOMMIT} == 0 )
{
$self->{DBH}->BEGIN; ## begin transaction
}
eval
{
$self->{STH} = $self->{DBH}->prepare($the_sql);

$self->{STH}->execute;

if ( $self->{THE_AUTOCOMMIT} == 0 )
{
$self->{DBH} ->commit() ## begin transaction
}

};

if ($@)
{
my $the_error = "The database server returned the following message:<br><br>$@";

$the_error = $the_error . "<br><br>The SQL pass to the database Server was:<br><br>$the_sql";

$self->error_form($the_error);

if ( $self->{THE_AUTOCOMMIT} == 0 )
{
eval
{
$self->{DBH}->rollback() ## in case rollback() fails
};
}

if( defined($self->{DBH}) ) {
$self->{DBH}->disconnect();
}

exit;

}
else
{
return $self->{STH};
}
}


I have also heard about GUID provided via web services which might be a good solution. You might do a google to see what you can find.

Regards,

LelandJ



Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox