Hi,
If the table has bar, baz set as the primary key it will only delete once.
In our case you should make smsc,ts the primary key. This also then has the
advantage of creating an implicit index on bar, baz.
create table foo(
bar int,
baz int,
PRIMARY KEY(bar, baz));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
for table "foo"
The "problem" with this approach is that the table structure will not accept
duplicates.
Another option is to use a serial column as it works well as shown below and
then we have replaced the previous Automatic OIDs with a sequential id.
OIDs are a scarce resource, and if you run low on them it all goes horribly
wrong as OIDs are used for all sorts of stuff internal to the database, so
if you use them all up on user tables it can cause problems.
Are you happy with using a sequential id or do you have another suggestion?
CREATE TABLE foo (
-- make the "id" column a primary key; this also creates
-- a UNIQUE constraint and a b+-tree index on the column
id SERIAL PRIMARY KEY,
bar int,
baz int
);
CREATE TABLE foo (
id SERIAL PRIMARY KEY,
bar int,
baz int );
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial
column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey"
for table "foo"
CREATE TABLE
dn=# insert into foo ( bar, baz ) values ( 1, 2 );
INSERT 447809901 1
dn=# insert into foo ( bar, baz ) values ( 1, 2 );
INSERT 447809902 1
dn=# delete from foo where id = (SELECT id FROM foo WHERE bar=1 and baz=2
limit 1);
DELETE 1
Regards
-----Original Message-----
From: Guillaume Cottenceau [mailto:[EMAIL PROTECTED]
Sent: 23 June 2008 10:20 AM
To: Hillel
Cc: [email protected]
Subject: Re: PATCH: Postgresql Patch
"Hillel" <hillel 'at' ecommunicate.biz> writes:
> Hi Kannel Devel,
>
> Attached and included in this email, is a postgresql patch that does not
use
> Automatic OIDs, which has been removed from Postgresql.
>
> You can make smsc,ts the primary key or as suggested by Guillaume
Cottenceau
> you can set smsc,ts as an index.
>
> diff -u gateway/gw/dlr_pgsql.c gateway/gw/dlr_pgsql.c
> --- gateway/gw/dlr_pgsql.c 2008-06-23 08:16:34.000000000 +0200
> +++ gateway/gw/dlr_pgsql.c 2008-06-22 12:27:38.000000000 +0200
> @@ -220,12 +220,11 @@
> Octstr *sql;
>
> debug("dlr.pgsql", 0, "removing DLR from database");
> - sql = octstr_format("DELETE FROM %s WHERE (%s,%s) IN (SELECT %s,%s
> WHERE %s='%s' AND %s='%s' LIMIT 1);",
The general contract of the code at that point is to delete only
one row even if there are multiple smsc,ts rows, which your
change breaks.
dn=# create table foo ( bar int, baz int );
CREATE TABLE
dn=# insert into foo ( bar, baz ) values ( 1, 2 );
INSERT 447809901 1
dn=# insert into foo ( bar, baz ) values ( 1, 2 );
INSERT 447809902 1
dn=# delete from foo where ( bar, baz ) in ( select 1, 2 limit 1 );
DELETE 2
Same for the UPDATE statement, actually.
--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36
