Re: [Handle-info] problems accessing postgres db

[Eric Auer <eric.auer@mpi.nl>]

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


Hi Konstantin,

when you home a NA, you write data into the
table "nas". The postgresql commands which
made our two Handle System tables here were:

create table nas (na bytea not null, primary key(na));

create table handles (handle bytea not null,
  idx int4 not null, type bytea, data bytea, ttl_type int2,
  ttl int4, timestamp int4, refs text, admin_read bool,
  admin_write bool, pub_read bool, pub_write bool,
  primary key(handle, idx));


Note that handle / data / na are all "bytea", so you need
some conversion to write a "varchar" into them... But you
have to make them "bytea" because the Handle System SQL
interface assumes that they are binary data, not strings.

So you either need a cast when YOU write a string manually,
when the columns are bytea, or you have to tell the Handle
System to do a cast when IT writes binary data, when the
columns are string. Examples for casting in postgres:

select sum(encode(data,'escape')::int8) from handles where type = 'FILESIZE';

select * from handles where handle like '42/123-%'::bytea and type = 'URL';


Another possible problem is that your column "handle" is "varchar(255)"
while it would probably be better to use "bytea" without size limit.


Note that our "refs" is "text" while it is "bytea" for you - but maybe
your choice is better and we simply did not encounter problems yet as
the column seems to be unused anyway: it always contains an empty string.

Actually only handle, idx, type and data are interesting for us.
Maybe it would be possible to replace the table by a view which
only pretends that the other columns would exist at all...

Eric


Konstantin Rekk wrote:
> have got some problems during homing a NA, I am using Postgres
> as Backend:
> 
> error.log:
> "2007/04/07 11:01:25 CEST" 75 class
> net.handle.server.HdlTcpRequestHandler: Exception processing request:
> HandleException (INTERNAL_ERROR) Error accessing NA data:
> org.postgresql.util.PSQLException: FEHLER: Operator existiert nicht:
> character varying = bytea
> 
> "FEHLER: Operator existiert nicht:" - "Failure: operator does not
> exist:"


> Here the handles table structure:
> ColumnName	Type	Size	Scale	Nullable/AutoIncrement	Type 
> handle	varchar	255	0	Not Null	false	12
> idx		int4	10	0	Not Null	false	4
> type		bytea	-1	0	Nullable	false	-2
> data		bytea	-1	0	Nullable	false	-2
... the other columns are: ttl_type int2 / ttl, timestamp int4,
refs bytea, {admin,pub}_{read,write} bool. All 0 scale, nullable,
non-autoincrement.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFGG2DJ99dkROyhRRsRAuMGAKC/k7UHmLjgXrlKBEVLqewXSXD/owCgwYj6
SzEJYnhUVyMtRx2PgV/ovQ8=
=xvXL
-----END PGP SIGNATURE-----


_______________________________________________
Handle-Info mailing list
Handle-Info@cnri.reston.va.us
http://www.handle.net/mailman/listinfo/handle-info