Postgres subquery aliasing

[Ben Hood]

Hi Lukas,

I'm wondering what is the most idiomatic way to encode the following
fragment in JOOQ:

SELECT (x).*, pg_try_advisory_xact_lock((x).id) AS locked
FROM (
SELECT x
FROM queues AS x
WHERE context = 'foo'
ORDER BY id, priority
LIMIT 1
) AS y;

I've tried to build the statement up from the middle, but I'm getting
snagged with my hacky way of using aliases:

db.select(DSL.field("x")).from(QUEUES.as("x")).
where(QUEUES.CONTEXT.eq("foo")).
orderBy(QUEUES.ID, QUEUES.PRIORITY).
limit(1).
fetchOne();

So JOOQ is probably doing the right thing by rendering this:

select x from "public"."queues" as "x" where
"public"."queues"."context" = 'foo' order by "public"."queues"."id"
asc, "public"."queues"."priority" asc limit 1 offset 0

But the DB is also correctly telling me that my alias is bogus:

Exception in thread "main" java.lang.RuntimeException:
org.postgresql.util.PSQLException: ERROR: invalid reference to
FROM-clause entry for table "queues"
Hint: Perhaps you meant to reference the table alias "x".
Position: 46

What would a Certified JOOQ Solution Engineer do?

Cheers,

Ben

[Lukas Eder]

Hi Ben,

What is (x).* and (x).id? I mean, there is no table called x in the outer query, only a table called y. Is x a user-defined type?

2014-11-20 12:20 GMT+01:00 Ben Hood <0x6e...@gmail.com>:

Hi Lukas,

I'm wondering what is the most idiomatic way to encode the following
fragment in JOOQ:

SELECT (x).*, pg_try_advisory_xact_lock((x).id) AS locked
FROM (
  SELECT x
  FROM queues AS x
  WHERE context = 'foo'
  ORDER BY id, priority
  LIMIT 1
) AS y;

I've tried to build the statement up from the middle, but I'm getting
snagged with my hacky way of using aliases:

db.select(DSL.field("x")).from(QUEUES.as("x")).
    where(QUEUES.CONTEXT.eq("foo")).
    orderBy(QUEUES.ID, QUEUES.PRIORITY).
    limit(1).
    fetchOne();

You're renaming QUEUES to "x", but then you're still using the QUEUES reference to dereference columns from. Try doing this:

Queues x = QUEUES.as("x")

And then

x.CONTEXT.eq("foo")

x.ID, x.PRIORITY

 

So JOOQ is probably doing the right thing by rendering this:

select x from "public"."queues" as "x" where
"public"."queues"."context" = 'foo' order by "public"."queues"."id"
asc, "public"."queues"."priority" asc limit 1 offset 0

But the DB is also correctly telling me that my alias is bogus:

Exception in thread "main" java.lang.RuntimeException:
org.postgresql.util.PSQLException: ERROR: invalid reference to
FROM-clause entry for table "queues"
  Hint: Perhaps you meant to reference the table alias "x".
  Position: 46

In other words, precisely what I said ;-)

[Ben Hood]

Hi Lukas,

Thanks for your help, it's very much appreciated.

On Thu, Nov 20, 2014 at 5:03 PM, Lukas Eder <lukas...@gmail.com> wrote:
> What is (x).* and (x).id? I mean, there is no table called x in the outer
> query, only a table called y. Is x a user-defined type?

x is not a user defined type, it is just an alias that I have used to
create a query. The query is quite large, so I just wanted to post you
a a fragment of the query so that you are not having to decipher weird
SQL.

> 2014-11-20 12:20 GMT+01:00 Ben Hood <0x6e...@gmail.com>:
> You're renaming QUEUES to "x", but then you're still using the QUEUES
> reference to dereference columns from. Try doing this:
>
> Queues x = QUEUES.as("x")
>
>
> And then
>
>
> x.CONTEXT.eq("foo")
> x.ID, x.PRIORITY

That works very well - I'd never actually used this form of "column
instantiation" (for want of a better term) in JOOQ before.

>> Exception in thread "main" java.lang.RuntimeException:
>> org.postgresql.util.PSQLException: ERROR: invalid reference to
>> FROM-clause entry for table "queues"
>> Hint: Perhaps you meant to reference the table alias "x".
>> Position: 46
>
>
> In other words, precisely what I said ;-)

Yes, you're probably looking at this and wondering what this guy is
trying to achieve. Maybe I should have given you the full context.
FWIW, this is the entire query (that works well when executed as a
regular SQL string):

WITH RECURSIVE message AS (

SELECT (x).*, pg_try_advisory_xact_lock((x).id) AS locked
FROM (
SELECT x
FROM queues AS x
WHERE context = 'foo'
ORDER BY id, priority
LIMIT 1
) AS y

UNION ALL (

SELECT (x).*, pg_try_advisory_xact_lock((x).id) AS locked
FROM (

SELECT (

SELECT x
FROM queues AS x
WHERE context = 'foo'

AND (id, priority) > (message.id, message.priority)

ORDER BY id, priority
LIMIT 1

) AS x
FROM message
WHERE NOT message.locked

LIMIT 1
) AS y

)
)
SELECT id, priority, enqueued_at, context, correlation_id
FROM message
WHERE locked
LIMIT 1;

Now that this query has the behavior I need, I am trying to
re-construct it using JOOQ.

Is this maybe something I shouldn't really be trying to do with JOOQ -
i.e. should I just use a plain SQL string or create a stored proc?

Cheers,

Ben

[Lukas Eder]

2014-11-20 17:58 GMT+01:00 Ben Hood <0x6e...@gmail.com>:

Hi Lukas,

Thanks for your help, it's very much appreciated.

On Thu, Nov 20, 2014 at 5:03 PM, Lukas Eder <lukas...@gmail.com> wrote:
> What is (x).* and (x).id? I mean, there is no table called x in the outer
> query, only a table called y. Is x a user-defined type?

x is not a user defined type, it is just an alias that I have used to
create a query. The query is quite large, so I just wanted to post you
a a fragment of the query so that you are not having to decipher weird
SQL.

I forgot about this syntax - just tried it on my PostgreSQL instance. So,

SELECT x

FROM queues AS x

or simply

SELECT queues

FROM queues

Is PostgreSQL-specific, and it selects the whole row as a single row column. There's actually mention of this feature in the following issue, which aims to add support for nested records in jOOQ:

https://github.com/jOOQ/jOOQ/issues/2360

OK, so that's clear now. However, you currently can't use that syntax in jOOQ. jOOQ cannot reason about DSL.field("x") being really a row with its nested columns.

> 2014-11-20 12:20 GMT+01:00 Ben Hood <0x6e...@gmail.com>:
> You're renaming QUEUES to "x", but then you're still using the QUEUES
> reference to dereference columns from. Try doing this:
>
> Queues x = QUEUES.as("x")
>
>
> And then
>
>
> x.CONTEXT.eq("foo")
> x.ID, x.PRIORITY

That works very well - I'd never actually used this form of "column
instantiation" (for want of a better term) in JOOQ before.

>> Exception in thread "main" java.lang.RuntimeException:
>> org.postgresql.util.PSQLException: ERROR: invalid reference to
>> FROM-clause entry for table "queues"
>>   Hint: Perhaps you meant to reference the table alias "x".
>>   Position: 46
>
>
> In other words, precisely what I said ;-)

Yes, you're probably looking at this and wondering what this guy is
trying to achieve. Maybe I should have given you the full context.

No worries. I was just confused by PostgreSQL syntax.

While you certainly can implement this with jOOQ (without using that PostgreSQL-specific syntax), there's nothing keeping you from writing a view (if you don't have bind variables), or a table-valued function via CREATE FUNCTION .. RETURNS TABLE:

http://www.postgresql.org/docs/9.3/static/sql-createfunction.html 

Those are supported by the jOOQ code generator since jOOQ 3.5:

https://github.com/jOOQ/jOOQ/issues/3375

The documentation is still missing, but let's assume that your function will take that 'foo' value as a parameter, only:

CREATE FUNCTION messages (p_context VARCHAR(100))

RETURNS TABLE (

    id INT,

    priority INT,

    locked INT

)

AS $$

BEGIN

    RETURN QUERY

    WITH RECURSIVE ...

END

$$ LANGUAGE plpgsql;

You can then create a function call to be used in SQL like this:

Messages messages = Tables.MESSAGES.call("foo");

select(messages.id, messages.priority, messages.locked)

.from(messages)

As you can see, the function call really returns an org.jooq.Table instance with column references that you can use. This is now supported for SQL Server (jOOQ 3.3), PostgreSQL (jOOQ 3.5), and Firebird (jOOQ 3.5).

Let me know if you want to go further into writing this query with jOOQ

Cheers

Lukas

[Ben Hood]

On Mon, Nov 24, 2014 at 8:28 AM, Lukas Eder <lukas...@gmail.com> wrote:
> OK, so that's clear now. However, you currently can't use that syntax in
> jOOQ. jOOQ cannot reason about DSL.field("x") being really a row with its
> nested columns.

Good to know.

> As you can see, the function call really returns an org.jooq.Table instance
> with column references that you can use. This is now supported for SQL
> Server (jOOQ 3.3), PostgreSQL (jOOQ 3.5), and Firebird (jOOQ 3.5).
>
> Let me know if you want to go further into writing this query with jOOQ

That's a very cool construct. Unfortunately I've just learned today
that the underlying business driver behind this "database as a queue"
solution has evaporated, so I'm going to have to put this example into
my JOOQ toolbox, ready for the next SQL battle.

Cheers,

Ben

[Lukas Eder]

Hi Ben,

Hmm, I thought that this was indeed a queue implementation. Very sophisticated, with recursive SQL. I'm personally curious (having recently written a somewhat controversial article on the subject: http://blog.jooq.org/2014/09/26/using-your-rdbms-for-messaging-is-totally-ok/). Were you at the point where you also evaluated the performance and locking implications of your implementation? It looks like you have implemented something similar to Oracle's magical FOR UDPATE SKIP LOCKED clause, which is the implementation basis of Oracle AQ that has been made public (= documented) with Oracle 10g, I believe.

Cheers,

Lukas

Cheers,

Ben

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ben Hood]

Hi Lukas,

On Tue, Nov 25, 2014 at 7:14 AM, Lukas Eder <lukas...@gmail.com> wrote:
> Hi Ben,
>
> Hmm, I thought that this was indeed a queue implementation. Very
> sophisticated, with recursive SQL. I'm personally curious (having recently
> written a somewhat controversial article on the subject:
> http://blog.jooq.org/2014/09/26/using-your-rdbms-for-messaging-is-totally-ok/).
> Were you at the point where you also evaluated the performance and locking
> implications of your implementation? It looks like you have implemented
> something similar to Oracle's magical FOR UDPATE SKIP LOCKED clause, which
> is the implementation basis of Oracle AQ that has been made public (=
> documented) with Oracle 10g, I believe.

TL;DR: I haven't benchmarked this implementation in real world
conditions, so I can't comment on real world performance.

And to be clear - the idea with the advisory lock is not my own - I
shamelessly stole it from here:
https://github.com/chanks/que/blob/master/lib/que/sql.rb

Back in the day I worked at an organization where AQ was our only
officially supported distributed choice of FIFO (i.e. Oracle was only
the piece of middleware we were allowed to run in production). 10g
used to have a limitation (not sure whether this is still true) that
the AQ proc for enqueuing and consuming point to point "messages" was
generally available, but the equivalent proc for pub-sub "messaging"
was implemented in a different package called something like
DBMS_AQ_ADMIN. Because of the name x_ADMIN, the DBAs refused execute
privileges to this package for non-DBA usage. So we ended up hacking a
polling based pub-sub solution. Basically we re-invented the wheel and
made turned it into a pentagon, but even pentagons can roll.

At the time, I was also part of the core RabbitMQ dev team, so I
benchmarked our solution against a Rabbit solution, and the AQ based
solution turned out to be a bit of a tortoise (legal disclaimer - for
the use case we were looking at, and also because I was fully aware of
the internals of Rabbit, so I knew where its sweet spot lay), but it
was fast enough for our purposes, so we stuck with. Rabbit was still
pre-1.0 and besides which, we would never have been allowed to run it
in production anyway.

I did briefly peruse at the AQ proc at the time, saw that it was using
a skip list, but I didn't go as far as profiling the IO
characteristics on the server, so I'm not exactly sure how smart the
AQ implementation is under the covers.

The main point of contention with using a DB as a queue is that to get
the FIFO semantics right in conjunction with exclusive consumption, in
the general case you need to lock a row. That's where Postgres has
this handy little proc which allows you to try to acquire an in-memory
mutex of your own liking. So cobbled together with a re-entrant query,
you can try to lock head of the queue, and in the instant that you
have an outstanding acknowledgement (i.e. a row has gone out over the
wire without a commit), you can skip to the nearest unlocked row.
Because the lock is in memory, there is no IO penalty. The downside is
that you can't scale this across multiple instances (assuming you've
mastered the art of multi-master writes in Postgres). But for old
school people like me who still believe in CAP, I avoid this issue by
only having a single master (i.e. I'm trading off write-availability
for consistency).

If you need distributed FIFOs, using a proper MQ usually doesn't get
you sacked these days. But even grown-up MQ's are subject to the CAP
triangle, so YMMV.

Ironically, back in the day, the design philosophy of RabbiMQ was to
deliver messages, not to queue them. It was only the fact that the
product had the letter Q in the name that led people to believe it was
a good idea to queue up messages. So then the MQ turned into a
database. Everything went full circle.

Personally I don't have any strong feelings either way. If you can
achieve you FIFO distribution requirements with your DB, you don't
have any flow control considerations and the performance penalty is
negligible, why not go for that? It saves you from having to deploy,
manage monitor another piece of infrastructure.

[Lukas Eder]

Hi Ben,

Wow, that is quite a bit of very interesting history you've shared right here on the jOOQ user group! I didn't know you were part of the RabbitMQ team. Very interesting to learn about all these backgrounds

DBAs... ;-)

Interesting. The enqueueing and dequeueing part is certainly not in DBMS_AQADM (anymore). It's all in DBMS_AQ. But setting up queues is in DBMS_AQADM for both producer/consumer and pub/sub queues:

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_aqadm.htm#CACDAABD

But in addition to the EXECUTE grant on the DBMS_AQ package, you also need additional "grants" that aren't really SQL grants but some PL/SQL admissions... It's a bit quirky - as many things in PL/SQL

I think it has always been like this, though...? Very interesting to implement a competing implementation of AQ on Oracle itself :-)

At the time, I was also part of the core RabbitMQ dev team, so I
benchmarked our solution against a Rabbit solution, and the AQ based
solution turned out to be a bit of a tortoise (legal disclaimer - for
the use case we were looking at, and also because I was fully aware of
the internals of Rabbit, so I knew where its sweet spot lay), but it
was fast enough for our purposes, so we stuck with. Rabbit was still
pre-1.0 and besides which, we would never have been allowed to run it
in production anyway.

I did briefly peruse at the AQ proc at the time, saw that it was using
a skip list, but I didn't go as far as profiling the IO
characteristics on the server, so I'm not exactly sure how smart the
AQ implementation is under the covers.

I haven't gone far either, but I believe that your PostgreSQL solution is pretty much the same thing. Except that it is very weird for a FOR UPDATE SKIP LOCKED clause to inject additional WHERE predicate semantics "after the fact", i.e. after ORDER BY has been applied, etc. So implementing FIFO queues with this SKIP LOCKED clause outside of AQ is rather tricky - albeit feasible.

As far as performance is concerned, AQ was a bottleneck in 11gR1 because of a significant concurrency bug that lead to Oracle having to be restarted in production every night for a couple of months. I believe that this is now fixed in 11gR2 and 12c, and AQ is running smoothly in that E-Banking system that I used to work on...

The main point of contention with using a DB as a queue is that to get
the FIFO semantics right in conjunction with exclusive consumption, in
the general case you need to lock a row. That's where Postgres has
this handy little proc which allows you to try to acquire an in-memory
mutex of your own liking. So cobbled together with a re-entrant query,
you can try to lock head of the queue, and in the instant that you
have an outstanding acknowledgement (i.e. a row has gone out over the
wire without a commit), you can skip to the nearest unlocked row.
Because the lock is in memory, there is no IO penalty. The downside is
that you can't scale this across multiple instances (assuming you've
mastered the art of multi-master writes in Postgres). But for old
school people like me who still believe in CAP, I avoid this issue by
only having a single master (i.e. I'm trading off write-availability
for consistency).

I'm in the same boat here. Don't scale out as long as you don't absolutely must. With RAM prices crumbling, scaling up is going to remain a very interesting and cheap (in terms of total cost of ownership) option for years to come. As I always say: Do not anger the mainframe :-)

If you need distributed FIFOs, using a proper MQ usually doesn't get
you sacked these days. But even grown-up MQ's are subject to the CAP
triangle, so YMMV.

Yep, it's hard. JavaEE makes a lot of promises about distributed transactions between databases and MQs. I have some experience with WLS, Oracle DB and IBM MQ, but we still occasionally had lost messages with years in production at a previous employer. Nonetheless, the person that acquired the unpayable IBM MQ licenses certainly didn't get sacked. Even if the licenses were so unreasonably expensive (price per MB transferred) that we rebuilt a complete document generation system for banking E-Documents just to avoid transferring the documents over the wire. Boy, I started loving XSL-FO layouting ;-)

Ironically, back in the day, the design philosophy of RabbiMQ was to
deliver messages, not to queue them. It was only the fact that the
product had the letter Q in the name that led people to believe it was
a good idea to queue up messages. So then the MQ turned into a
database. Everything went full circle.

Well, people never listen :-) At this point, citing Gavin King seems appropriate:

​Full circle. Hibernate can now handle unmanaged DTOs and batch DML ;-)

 

Personally I don't have any strong feelings either way. If you can
achieve you FIFO distribution requirements with your DB, you don't
have any flow control considerations and the performance penalty is
negligible, why not go for that? It saves you from having to deploy,
manage monitor another piece of infrastructure.

Exactly. The RDBMS is a hammer, many problems are nails. I mean, the hammer is a crazy Swiss Army Knife Hammer, so it won't let you down for quite a while before you need something else.

Cheers,

Lukas

[Ben Hood]

Interestingly enough, here is a recent take on the queue CTE mentioned above:

https://brandur.org/postgres-queues

[Lukas Eder]

Very interesting share, thanks a lot!

--