Thread: feature request ctid cast / sql exception
Hello,
this is a feature request for a rather simple functionality.
I propose to implement a builtin and efficient bidirectional cast between ctid and bigint types.
Another nice feature would be a function that can be called from a sql statement and would throw an exception when executed.
I know these functions can be implemented using UDF, but the performance and need to deploy it to every database is very inconvenient.
Thank you
On Sat, Apr 17, 2021 at 10:58 AM Vladimír Houba ml. <v.houba@gmail.com> wrote:
I propose to implement a builtin and efficient bidirectional cast between ctid and bigint types.
Why?
Another nice feature would be a function that can be called from a sql statement and would throw an exception when executed.
An assertion-related extension in core would be welcomed.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Apr 17, 2021 at 10:58 AM Vladimír Houba ml. <v.houba@gmail.com>
> wrote:
>> Another nice feature would be a function that can be called from a sql
>> statement and would throw an exception when executed.
> An assertion-related extension in core would be welcomed.
This has been suggested before, but as soon as you start looking
at the details you find that it's really hard to get a one-size-fits-all
definition that's any simpler than the existing plpgsql RAISE
functionality. Different people have different ideas about how
much decoration they want around the message. So, if 10% of the
world agrees with your choices and the other 90% keeps on using
a custom plpgsql function to do it their way, you haven't really
improved matters much. OTOH a 90% solution might be interesting to
incorporate in core, but nobody's demonstrated that one exists.
regards, tom lane
I use ctid as a row identifier within a transaction in a Java application.
To obtain the row ctid I either have to
- cast it to text and store it as String
- cast it to string, then convert it to a bigint using UDF which is inefficient
I wish I could just cast ctid to bigint and store it as a primitive long type.
Regarding the exception throwing function it makes good sense for example in case blocks when you encouter unexpected value.
IMHO "one fits all" solution may be making a raise function with the same syntax as raise statement in plpgsql.
RAISE([ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ])
RAISE([ level ] condition_name [ USING option = expression [, ... ] ])
RAISE([ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ])
RAISE([ level ] USING option = expression [, ... ])
RAISE()
RAISE([ level ] condition_name [ USING option = expression [, ... ] ])
RAISE([ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ])
RAISE([ level ] USING option = expression [, ... ])
RAISE()
On Sat, Apr 17, 2021 at 9:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Apr 17, 2021 at 10:58 AM Vladimír Houba ml. <v.houba@gmail.com>
> wrote:
>> Another nice feature would be a function that can be called from a sql
>> statement and would throw an exception when executed.
> An assertion-related extension in core would be welcomed.
This has been suggested before, but as soon as you start looking
at the details you find that it's really hard to get a one-size-fits-all
definition that's any simpler than the existing plpgsql RAISE
functionality. Different people have different ideas about how
much decoration they want around the message. So, if 10% of the
world agrees with your choices and the other 90% keeps on using
a custom plpgsql function to do it their way, you haven't really
improved matters much. OTOH a 90% solution might be interesting to
incorporate in core, but nobody's demonstrated that one exists.
regards, tom lane
S pozdravom
Vladimír Houba ml.
Vladimír Houba ml.
On Sat, Apr 17, 2021 at 12:58 PM Vladimír Houba ml. <v.houba@gmail.com> wrote:
I use ctid as a row identifier within a transaction in a Java application.
This doesn't present a very compelling argument since an actual user declared primary key is what is expected to be used as a row identifier. And as those are typically bigint if you follow this norm you get exactly what you say you need.
David J.
On Saturday, April 17, 2021, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Apr 17, 2021 at 10:58 AM Vladimír Houba ml. <v.houba@gmail.com>
> wrote:
>> Another nice feature would be a function that can be called from a sql
>> statement and would throw an exception when executed.
> An assertion-related extension in core would be welcomed.
This has been suggested before, but as soon as you start looking
at the details you find that it's really hard to get a one-size-fits-all
definition that's any simpler than the existing plpgsql RAISE
functionality.
Even just getting raise functionality as a standard functional api would be a win. I don’t imagine enough users would care enough to write their own routines if one already existed, even if they would argue details about how to create it in the first place. For the expected use case of basically developer-oriented error messages there is generally a acceptance of taking the sufficient solution.
David J.
This is a specific use case, I have a big table without a pk. Updates with ctid are blazing fast even without an index. I dont need it.
The argument behind this is that users excpect this functionality, its not just me. Search stackoverflow. They end up using various suboptimal solutions as I described earlier. This is a very very simple functionality so please consider it. Im also writing an opensource lib that would make use of this. My users will be thankfull to you.
On Sat, Apr 17, 2021, 23:05 David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sat, Apr 17, 2021 at 12:58 PM Vladimír Houba ml. <v.houba@gmail.com> wrote:I use ctid as a row identifier within a transaction in a Java application.This doesn't present a very compelling argument since an actual user declared primary key is what is expected to be used as a row identifier. And as those are typically bigint if you follow this norm you get exactly what you say you need.David J.