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
Re: feature request ctid cast / sql exception
On Sat, Apr 17, 2021 at 10:58 AM Vladimír Houba ml. 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.
Re: feature request ctid cast / sql exception
"David G. Johnston" writes: > On Sat, Apr 17, 2021 at 10:58 AM Vladimír Houba ml. > 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
Re: feature request ctid cast / sql exception
On Sat, Apr 17, 2021 at 12:58 PM Vladimír Houba ml. 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.
Re: feature request ctid cast / sql exception
On Saturday, April 17, 2021, Tom Lane wrote: > "David G. Johnston" writes: > > On Sat, Apr 17, 2021 at 10:58 AM Vladimír Houba ml. > > 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.
Re: feature request ctid cast / sql exception
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() On Sat, Apr 17, 2021 at 9:46 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Sat, Apr 17, 2021 at 10:58 AM Vladimír Houba ml. > > 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.
Re: feature request ctid cast / sql exception
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 wrote: > On Sat, Apr 17, 2021 at 12:58 PM Vladimír Houba ml. > 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. > >
