Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to combine multiple field primary key and Row Versioning

How to combine multiple field primary key and Row Versioning

From
DI Hasenöhrl
Date:
Hello,
 
I use Postgrsql 7.0.2 and ODBC 7.1.003. I discovered, that Row Versioning doesn't work with multiple field primary keys.
I need Row Versioning to be able to change tuples of a table, inserted by a function and I use multiple field primary keys to be sure, that inserted data are correct ie table *sortiment* contains the articles of each year
create table sortiment(
    year        integer,
    article_nr integer,
    number    integer,
    primary key(year,article_nr)
);
 
Are there further releases, which can combine multiple field primary keys and Row Versioning, or does anyone have any ideas, how to solve this confict?
 
Thanks in advance for any hint
Irina
 

RE: How to combine multiple field primary key and Row Versioning

From
"Henshall, Stuart - WCP"
Date:
Hello,
    I suspect that it doesn't have the xid=int4 operator. Try adding the
following:

create function int4eq(xid,int4)
    returns bool
    as ''
    language 'internal';

create operator = (
    leftarg=xid,
    rightarg=int4,
    procedure=int4eq,
    commutator='=',
    negator='<>',
    restrict=eqsel,
    join=eqjoinsel
);

I'm assuming the server reported an error something like:
ERROR:  Unable to identify an operator '=' for types 'xid' and 'int4'
        You will have to retype this query using an explicit cast
Hope this helps,
- Stuart

> -----Original Message-----
> From:    DI Hasenöhrl [SMTP:i.hasenoehrl@aon.at]
> Sent:    Tuesday, June 26, 2001 11:37 AM
> To:    pgsql-odbc@postgresql.org
> Subject:    How to combine multiple field primary key and Row Versioning
>
> Hello,
>
> I use Postgrsql 7.0.2 and ODBC 7.1.003. I discovered, that Row Versioning
> doesn't work with multiple field primary keys.
> I need Row Versioning to be able to change tuples of a table, inserted by
> a function and I use multiple field primary keys to be sure, that inserted
> data are correct ie table *sortiment* contains the articles of each year
>
>     create table sortiment(
>         year        integer,
>         article_nr integer,
>         number    integer,
>         primary key(year,article_nr)
>     );
>
>
> Are there further releases, which can combine multiple field primary keys
> and Row Versioning, or does anyone have any ideas, how to solve this
> confict?
>
> Thanks in advance for any hint
> Irina
>
> E-Mail: i.hasenoehrl@aon.at <mailto:i.hasenoehrl@aon.at>

Re: How to combine multiple field primary key and Row Versioning

From
DI Hasenöhrl
Date:
Hello Stuart,
 
Thank you for your hint. I overloaded the = operator for xid,int4.
The problem is caused by multiple field primary key; it works with a single field primary key.
I got the hint to use a *dummy* single field primary key and use unique indices. I have to change all tables, if there is no other way to work with multiple field primary key and Row Versioning.
 
Did you already find a fast solution for combo boxes? Since now I was not lucky. I tried to use *OpenRecordset with( dbopendynamic ) * to get automatically changes made by an other user, but this didn't work the way I expected.
1. I can't see the changes made by an other user
2. my user defined function, which uses the dynamic recordset as rowsource, for the combo box is slower than rowsource type via linked table
 
My form becomes slow, because I use some combo boxes which depend on other combo boxes. I think, that speed would increase if I find a way to write pass through queries with parameters.
 
Kind regards
Irina
 
 
----- Original Message -----
Sent: Thursday, June 28, 2001 10:24 AM
Subject: RE: How to combine multiple field primary key and Row Versioning

Hello,
I suspect that it doesn't have the xid=int4 operator. Try adding the
following:

create function int4eq(xid,int4)
    returns bool
    as ''
    language 'internal';

create operator = (
    leftarg=xid,
    rightarg=int4,
    procedure=int4eq,
    commutator='=',
    negator='<>',
    restrict=eqsel,
    join=eqjoinsel
);

I'm assuming the server reported an error something like:
ERROR:  Unable to identify an operator '=' for types 'xid' and 'int4'
        You will have to retype this query using an explicit cast
Hope this helps,
- Stuart

> -----Original Message-----
> From: DI Hasenöhrl [SMTP:i.hasenoehrl@aon.at]
> Sent: Tuesday, June 26, 2001 11:37 AM
> To: pgsql-odbc@postgresql.org
> Subject: How to combine multiple field primary key and Row Versioning
>
> Hello,
> 
> I use Postgrsql 7.0.2 and ODBC 7.1.003. I discovered, that Row Versioning
> doesn't work with multiple field primary keys.
> I need Row Versioning to be able to change tuples of a table, inserted by
> a function and I use multiple field primary keys to be sure, that inserted
> data are correct ie table *sortiment* contains the articles of each year
>
> create table sortiment(
>     year        integer,
>     article_nr integer,
>     number    integer,
>     primary key(year,article_nr)
> );
>
>
> Are there further releases, which can combine multiple field primary keys
> and Row Versioning, or does anyone have any ideas, how to solve this
> confict?
> 
> Thanks in advance for any hint
> Irina
> 
> E-Mail: i.hasenoehrl@aon.at <mailto:i.hasenoehrl@aon.at>