Skip site navigation (1) Skip section navigation (2)

proposal: Preference SQL

From: Jan Urbański <j(dot)urbanski(at)students(dot)mimuw(dot)edu(dot)pl>
To: Postgres - Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: proposal: Preference SQL
Date: 2008-05-29 23:08:53
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
This is a proposal of a non-standard, albeit useful functionality in 
Postgres (it is also a quite long email message).

I'm currently working on a GSoC project for PostgreSQL 
( But at the same time, I'm 
now at the point where I need to choose a subject for my thesis. 
Naturally, I thought of PG.
I have an idea to add a feature, that no other database currently has (I 
haven't heard of it, anyway) and that could be potentially quite useful. 
If not, at
least it'd be a great bragging point ;) But seriously, this could be 
quite nice to have.

I'll have two years to complete my thesis, maybe not dedicating 100% of 
my time to it, but that's still plenty of time to carefully design and 
a complex functionality.

What I need is some kind of decision: is it possible to eventually get 
this feature integrated into core? Is it at all wanted? If not, I think 
I'll carry on with implementing is just for the sake of my thesis and of 
course my coding standards will be much lower (just joking :]). But if 
there will be interest in doing it properly and releasing for the 
general public, I'd try much harder to make it fast, portable, etc. etc. 
adding work, but forseeing glory.

Now about the idea itself:
That's one of the basic papers about Preference SQL, explaining what 
it's all about. For those, who don't feel like reading through it just 
because I said it's interesting, here's some more info (warning, it's a 
bit formal):

Preference SQL is an extension to regular SQL, that allows expressing 
preferences in SQL queries. Preferences are like "soft" WHERE clauses. A 
preference doesn't need to be satisfied by a tuple for it to appear in 
the result set, but it's "preferred" it is. More strictly, a set of 
preference clauses in a SQL query defines a partial order on the result 
set as it would appear without any preference clauses and then returns 
the maximal elements.
The partial order imposed by the set of preferences P[1], P[2], ..., 
P[n] is such that tuple T1 > T2  iff  T1 all preferences T2 satisfies 
and there is a preference satisfied by T1 and not satisfied by T2 (or 
there is a preference satisfied by T1 that is "better" satisfied by T2 
and all others are "equaly" satisfied). As can be seen, there could be 
an order defined on the degree of satisfyiness of a preference, and the 
exact semantics are not all that well defined for all concievable cases. 
Defining a complete semantics will be a part of my thesis.

An example of a preference query would be (quoting the linked PDF):

SELECT * FROM programmers PREFERRING exp IN (‘java’, ‘C++’);


SELECT * FROM computers
PREFERRING HIGHEST(main_memory) AND HIGHEST(cpu_speed);

I guess you get the idea when looking at these queries, but some cases 
can be tricky, and also there's more to preference SQL that explained 
earlier. For more, refer to the aforementioned PDF, it's not that scary 
at all.

Desing, implementation:
Nothing done, nothing even thought about as of now. I'm concentrating on 
my GSoC project ;) But I'll have lots and lots of time for this, and 
even as it will not be my priority thing to do (it *is* a thesis 
subject, after all) it still think it's possible to implement.
I have seen two people doing a preference XPath implementation by 
hacking Saxon in about three months. I guess it didn't support all of 
it, I guess it was slow and more of a PoC and I'm sure it had a ton of 
bugs, but it was quite impressive nonetheless.

So what say you? Preferring?


Jan Urbanski
GPG key ID: E583D7D2

ouden estin


pgsql-hackers by date

Next:From: Joshua D. DrakeDate: 2008-05-29 23:12:22
Subject: Re: Core team statement on replication in PostgreSQL
Previous:From: Tom LaneDate: 2008-05-29 23:02:56
Subject: Re: Core team statement on replication in PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group