Postgres Pro
Downloads
Home   >   mailing lists

Thread: pre-parser query manipulation

pre-parser query manipulation

From
Randall Smith
Date:
Hi,

I'm attempting to process a query written in the form:

     SELECT fields FROM table WHERE conditions FOR UPDATE OF field_name

when Postgresql supports:

     SELECT fields FROM table WHERE conditions FOR UPDATE OF table_name

I'd like to remove the OF clause in the case only one table is queried.

Anyone know of a solution to this?

Randall

Re: pre-parser query manipulation

From
Erik Jones
Date:
This may be a dupe.  I sent and had some issues with my mail client and afterwards it was sitting all alone in my outbox, if you've already seen this, sorry for the resend...

On Mar 14, 2007, at 2:08 PM, Randall Smith wrote:

Hi,

I'm attempting to process a query written in the form:

    SELECT fields FROM table WHERE conditions FOR UPDATE OF field_name

when Postgresql supports:

    SELECT fields FROM table WHERE conditions FOR UPDATE OF table_name

I'd like to remove the OF clause in the case only one table is queried.

Anyone know of a solution to this?

The OF clause is optional in postrges.  The semantics are thus:

Without OF clause:  lock all rows in all tables in the from clause that contribute data to the rows returned by your select statement.  So, if I'm understanding what you're asking here, just chop off the OF clause and you're set.

With OF clause: lock only rows from the specified tables that contribute data to the rows returned by your select statement.

There isn't any way that I know of to lock specific columns on a table as it seems the first query you listed is doing.

erik jones <erik@myemma.com>
sofware developer
615-296-0838
emma(r)



Re: pre-parser query manipulation

From
Randall Smith
Date:
Erik Jones wrote:
> This may be a dupe.  I sent and had some issues with my mail client and
> afterwards it was sitting all alone in my outbox, if you've already seen
> this, sorry for the resend...
>
> On Mar 14, 2007, at 2:08 PM, Randall Smith wrote:
>
>> Hi,
>>
>> I'm attempting to process a query written in the form:
>>
>>     SELECT fields FROM table WHERE conditions FOR UPDATE OF field_name
>>
>> when Postgresql supports:
>>
>>     SELECT fields FROM table WHERE conditions FOR UPDATE OF table_name
>>
>> I'd like to remove the OF clause in the case only one table is queried.
>>
>> Anyone know of a solution to this?
>
> The OF clause is optional in postrges.  The semantics are thus:
>
> Without OF clause:  lock all rows in all tables in the from clause that
> contribute data to the rows returned by your select statement.  So, if
> I'm understanding what you're asking here, just chop off the OF clause
> and you're set.

Didn't know that.  Thanks.

Chopping off the OF clause is the problem.  I have control of the DB,
but not of the application.  I do have control over the JDBC driver so I
could attack it there, but it would be neat to intercept the query
before the parser gets it and chop off the OF.  Any idea how to do that?
  Rules work after the parser, right?

>
> With OF clause: lock only rows from the specified tables that contribute
> data to the rows returned by your select statement.
>
> There isn't any way that I know of to lock specific columns on a table
> as it seems the first query you listed is doing.

Oracle likes it for some reason even though I think it only does row
level locking.

Randall

Re: pre-parser query manipulation

From
Randall Smith
Date:
Erik Jones wrote:
> This may be a dupe.  I sent and had some issues with my mail client and
> afterwards it was sitting all alone in my outbox, if you've already seen
> this, sorry for the resend...
>
> On Mar 14, 2007, at 2:08 PM, Randall Smith wrote:
>
>> Hi,
>>
>> I'm attempting to process a query written in the form:
>>
>>     SELECT fields FROM table WHERE conditions FOR UPDATE OF field_name
>>
>> when Postgresql supports:
>>
>>     SELECT fields FROM table WHERE conditions FOR UPDATE OF table_name
>>
>> I'd like to remove the OF clause in the case only one table is queried.
>>
>> Anyone know of a solution to this?
>
> The OF clause is optional in postrges.  The semantics are thus:
>
> Without OF clause:  lock all rows in all tables in the from clause that
> contribute data to the rows returned by your select statement.  So, if
> I'm understanding what you're asking here, just chop off the OF clause
> and you're set.
>
> With OF clause: lock only rows from the specified tables that contribute
> data to the rows returned by your select statement.
>
> There isn't any way that I know of to lock specific columns on a table
> as it seems the first query you listed is doing.
>
> erik jones <erik@myemma.com <mailto:erik@myemma.com>>
> sofware developer
> 615-296-0838
> emma(r)
>
>
>

I'm at a loss as to how to remove the OF clause at the server.  It it
possible to do it with a rule?  I don't have control over the
application and it's written to work with Oracle.  I do have control
over the JDBC driver, so maybe I should look for a solution there.
Maybe a wrapper or something.

Randall

Re: pre-parser query manipulation

From
Tom Lane
Date:
Randall Smith <randall@tnr.cc> writes:
> I'm at a loss as to how to remove the OF clause at the server.  It it
> possible to do it with a rule?

No, because the syntax error will occur long before any rule has a
chance to get involved.  You'd have to actually hack the C code
(see transformLockingClause in src/backend/parser/analyze.c).

It's unfortunate that somebody decided to use the standard FOR UPDATE
syntax to mean something that's got nearly 0 to do with what the spec
intends it to mean :-(.

            regards, tom lane