Re: [GENERAL] pgsql 'prefix' error
Hi, Dimitri Fontaine writes: > Can you test with this version and maybe better data set? [...] > Of course changing that will discard any btree containing a prefix_range > column, so that's going to be 1.1.0 if workable. http://github.com/dimitri/prefix http://github.com/dimitri/prefix/commit/e1bcb8e28305c9257655548a70c9fc05a21e9a1e It's being uploaded to debian now. I'm going to release 1.1.0 source tarball (only) on pgfoundry. Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgsql 'prefix'
Hi, Le 23 nov. 2009 à 17:04, Harald Fuchs a écrit : > SELECT id, record > FROM myrecords > WHERE record @> '127' > ORDER BY length(record::text) DESC > LIMIT 1; In prefix 1.0.0 you can say ORDER BY length(record) DESC directly... -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgsql 'prefix' error
In article <87tywid19x@hi-media-techno.com>,
Dimitri Fontaine writes:
> The BTree opclass is not made to resist to overlapping data. Maybe in
> this case though we could say that 12 contains less elements than 1 so
> it's less than 1. Here's a test to redefine the pr_cmp() operator in
> term of that, as a patch against current CVS (which is 1.0.0).
> Can you test with this version and maybe better data set?
Looks good.
> Note that as said earlier the indexing you need to speed up queries is
> the GiST one, but it could be you want the PK constraint noneless.
Indeed - I think it's a good thing to be able to prevent something
like
INSERT INTO myrecords (record) VALUES ('12'), ('12');
Oh, here's another gripe: how about renaming README.txt to README.prefix
and removing TESTS.* from "make install"?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgsql 'prefix' error
Hi,
Bino Oetomo writes:
> ERROR: duplicate key value violates unique constraint "myrecords_pkey"
> CONTEXT: COPY myrecords, line 2: "12"
I think I should add the following code comment to the documentation, if
not already done:
/*
* We invent a prefix_range ordering for convenience, but that's
* dangerous. Use the BTree opclass at your own risk.
*
* On the other hand, when your routing table does contain pretty static
* data and you test it carefully or know it will fit into the ordering
* simplification, you're good to go.
*
* Baring bug, the constraint is to have non-overlapping data.
*/
You have to remember that '12'::prefix_range could be spelled as the
regular expression '12.*'. So that '1'::prefix_range contains '12'.
The BTree opclass is not made to resist to overlapping data. Maybe in
this case though we could say that 12 contains less elements than 1 so
it's less than 1. Here's a test to redefine the pr_cmp() operator in
term of that, as a patch against current CVS (which is 1.0.0).
Can you test with this version and maybe better data set?
Note that as said earlier the indexing you need to speed up queries is
the GiST one, but it could be you want the PK constraint noneless.
prefix=# select prefix_range_cmp('1', '12');
prefix_range_cmp
--
1 -- it is 0 without the patch.
(1 row)
This means '1'::prefix_range > '12'::prefix_range and you're now able to
create your PRIMARY KEY on the example data. It's still not very useful
for the general case, but could be argued as better...
Of course changing that will discard any btree containing a prefix_range
column, so that's going to be 1.1.0 if workable.
Regards,
--
dim
PS: no worry about the operators themselves, they are defined atop cmp:
static inline
bool pr_lt(prefix_range *a, prefix_range *b, bool eqval) {
int cmp = pr_cmp(a, b);
return eqval ? cmp <= 0 : cmp < 0;
}
static inline
bool pr_gt(prefix_range *a, prefix_range *b, bool eqval) {
int cmp = pr_cmp(a, b);
return eqval ? cmp >= 0 : cmp > 0;
}
? README.html
? TESTS.html
? prefix.sql
? prefixes.check.pl
? todo
? varlena.gavin.snippet.c
? debian/files
? debian/postgresql-8.3-prefix
? debian/postgresql-8.3-prefix.debhelper.log
? debian/postgresql-8.3-prefix.substvars
? debian/postgresql-8.4-prefix
? debian/postgresql-8.4-prefix.debhelper.log
? debian/postgresql-8.4-prefix.substvars
Index: prefix.c
===
RCS file: /cvsroot/prefix/prefix/prefix.c,v
retrieving revision 1.54
diff -p -u -r1.54 prefix.c
--- prefix.c 6 Oct 2009 09:55:32 - 1.54
+++ prefix.c 25 Nov 2009 16:10:47 -
@@ -520,7 +520,11 @@ int pr_cmp(prefix_range *a, prefix_range
cmp = memcmp(p, q, mlen);
if( cmp == 0 )
-return (a->first == b->first) ? (a->last - b->last) : (a->first - b->first);
+/*
+ * we are comparing e.g. '1' and '12' (the shorter contains the
+ * smaller), so let's pretend '12' < '1' as it contains less elements.
+ */
+return (alen == mlen) ? 1 : -1;
return cmp;
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgsql 'prefix' error
Dear Harald Harald Fuchs wrote: At least in prefix 1.0.0 unique indexes seem to be broken. Just drop the primary key and add a separate index: CREATE INDEX myrecords_record_ix ON myrecords USING gist (record); Yup .. it works now. Thankyou for your enlightment Sincerely -bino- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgsql 'prefix' error
In article <4b0bbc8e.6010...@indoakses-online.com>, Bino Oetomo writes: > I downloaded pgfoundry's prefix, postgresql-8.3-prefix_1.0.0-1_i386.deb > I install it using dpkg , and run the prefix.sql > Create database .. named 'prefbino', and > CREATE TABLE myrecords ( > record prefix_range NOT NULL, > PRIMARY KEY (record) > ); > Looks good, next > I try to create some records, But I got this error : > ---START-- > prefbino=# COPY myrecords (record) FROM stdin; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself. >>> 1 >>> 12 >>> 123 >>> 1234 >>> \. > ERROR: duplicate key value violates unique constraint "myrecords_pkey" > CONTEXT: COPY myrecords, line 2: "12" > ---STOP-- > Kindly please give me further enlightment At least in prefix 1.0.0 unique indexes seem to be broken. Just drop the primary key and add a separate index: CREATE INDEX myrecords_record_ix ON myrecords USING gist (record); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgsql 'prefix' error
Dear All Harald Fuchs wrote: For larger tables where an index search would be useful, check out pgfoundry.org/projects/prefix: CREATE TABLE myrecords ( record prefix_range NOT NULL, PRIMARY KEY (record) ); COPY myrecords (record) FROM stdin; 1 12 123 1234 \. I downloaded pgfoundry's prefix, postgresql-8.3-prefix_1.0.0-1_i386.deb I install it using dpkg , and run the prefix.sql Create database .. named 'prefbino', and CREATE TABLE myrecords ( record prefix_range NOT NULL, PRIMARY KEY (record) ); Looks good, next I try to create some records, But I got this error : ---START-- prefbino=# COPY myrecords (record) FROM stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1 >> 12 >> 123 >> 1234 >> \. ERROR: duplicate key value violates unique constraint "myrecords_pkey" CONTEXT: COPY myrecords, line 2: "12" ---STOP-- Kindly please give me further enlightment Sincerely -bino- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
