Thread: regclass without error?
Hi, Is there any way to use regclass without having ERROR? pgpool-II needs to find the oid from table name and for the purpose it issues something like "SELECT 'table_name'::regproc::oid". Problem is, if the table does not exist, an error occured and the transaction aborts. Ideally if the table does not exist, the SELECT returns 0 (InvalidOid). Any idea? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Fri, Sep 3, 2010 at 4:28 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: > Is there any way to use regclass without having ERROR? > > pgpool-II needs to find the oid from table name and for the purpose it > issues something like "SELECT 'table_name'::regproc::oid". Problem is, > if the table does not exist, an error occured and the transaction > aborts. Ideally if the table does not exist, the SELECT returns 0 > (InvalidOid). > > Any idea? You can write a query against the system catalog tables. Or you could install a function that wraps the regclass cast in an exception handler. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
> You can write a query against the system catalog tables. That was pretty hard than I though(schema search path etc.). > Or you could > install a function that wraps the regclass cast in an exception > handler. That requires users to install the function. Annoying for users. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On 2010-09-03 4:46 PM, Tatsuo Ishii wrote: >> You can write a query against the system catalog tables. > > That was pretty hard than I though(schema search path etc.). > >> Or you could >> install a function that wraps the regclass cast in an exception >> handler. > > That requires users to install the function. Annoying for users. How about using a SAVEPOINT before the cast? Wouldn't fail your transaction.. Regards, Marko Tiikkaja
On Fri, Sep 03, 2010 at 05:10:44PM +0300, Marko Tiikkaja wrote: > On 2010-09-03 4:46 PM, Tatsuo Ishii wrote: > >>You can write a query against the system catalog tables. > > > >That was pretty hard than I though(schema search path etc.). > > > >>Or you could > >>install a function that wraps the regclass cast in an exception > >>handler. > > > >That requires users to install the function. Annoying for users. > > How about using a SAVEPOINT before the cast? Wouldn't fail your > transaction.. For unattended operation, there are some issues: * Generating appropriate SAVEPOINT names * Keeping track of same * Detecting errors * Issuing ROLLBACKs to the aforementioned SAVEPOINT None of this works super well for a bulk load. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Tatsuo Ishii <ishii@postgresql.org> writes:
> Is there any way to use regclass without having ERROR?
> pgpool-II needs to find the oid from table name and for the purpose it
> issues something like "SELECT 'table_name'::regproc::oid". Problem is,
> if the table does not exist, an error occured and the transaction
> aborts. Ideally if the table does not exist, the SELECT returns 0
> (InvalidOid).
I don't think the cast should act that way, but I could see providing a
separate conversion function that returns 0 ... or perhaps better NULL
... if no match.
regards, tom lane
Hello, guys. You wrote: TI> Hi, TI> Is there any way to use regclass without having ERROR? TI> pgpool-II needs to find the oid from table name and for the purpose it TI> issues something like "SELECT 'table_name'::regproc::oid". Problem is, TI> if the table does not exist, an error occured and the transaction TI> aborts. Ideally if the table does not exist, the SELECT returns 0 TI> (InvalidOid). Agreed with Tatsuo about having InvalidOid. TI> Any idea? TI> -- TI> Tatsuo Ishii TI> SRA OSS, Inc. Japan TI> English: http://www.sraoss.co.jp/index_en.php TI> Japanese: http://www.sraoss.co.jp -- With best wishes,Pavel mailto:pavel@gf.microolap.com
On Sep 3, 2010, at 7:31 AM, Tom Lane wrote: > I don't think the cast should act that way, but I could see providing a > separate conversion function that returns 0 ... or perhaps better NULL > ... if no match. +1 I could use this in pgTAP. David
Excerpts from David Fetter's message of vie sep 03 10:21:00 -0400 2010: > > How about using a SAVEPOINT before the cast? Wouldn't fail your > > transaction.. > > For unattended operation, there are some issues: > > * Generating appropriate SAVEPOINT names > * Keeping track of same > * Detecting errors > * Issuing ROLLBACKs to the aforementioned SAVEPOINT > > None of this works super well for a bulk load. Why do you need any of this? The logic is pretty simple: SAVEPOINT my_savepoint; select ...::regclass; -- if it works RELEASE my_savepoint; -- if it fails ROLLBACK TO my_savepoint; RELEASE my_savepoint; Granted, it's not super-performant ... -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, Sep 03, 2010 at 01:28:15PM -0400, Alvaro Herrera wrote: > Excerpts from David Fetter's message of vie sep 03 10:21:00 -0400 2010: > > > > How about using a SAVEPOINT before the cast? Wouldn't fail your > > > transaction.. > > > > For unattended operation, there are some issues: > > > > * Generating appropriate SAVEPOINT names > > * Keeping track of same > > * Detecting errors > > * Issuing ROLLBACKs to the aforementioned SAVEPOINT > > > > None of this works super well for a bulk load. > > Why do you need any of this? The logic is pretty simple: > > SAVEPOINT my_savepoint; > select ...::regclass; > > -- if it works > RELEASE my_savepoint; > > -- if it fails > ROLLBACK TO my_savepoint; > RELEASE my_savepoint; > > Granted, it's not super-performant ... We have no mechanism to do this, or any other check, during a bulk load. It'd be a great feature to have :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Excerpts from David Fetter's message of vie sep 03 13:31:00 -0400 2010: > We have no mechanism to do this, or any other check, during a bulk > load. It'd be a great feature to have :) I'm not sure what kind of bulk load you are talking about, nor what does it have to do with pgpool-II. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> Tatsuo Ishii <ishii@postgresql.org> writes: > > Is there any way to use regclass without having ERROR? > > > pgpool-II needs to find the oid from table name and for the purpose it > > issues something like "SELECT 'table_name'::regproc::oid". Problem is, > > if the table does not exist, an error occured and the transaction > > aborts. Ideally if the table does not exist, the SELECT returns 0 > > (InvalidOid). > > I don't think the cast should act that way, but I could see providing a > separate conversion function that returns 0 ... or perhaps better NULL > ... if no match. Such a function should be very helpfull. Great! -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Long time ago, I propose regclass like function which does not throw
an error if the table is not found. Instead I want to let it return
InvalidOid or NULL.
>> Tatsuo Ishii <ishii@postgresql.org> writes:
>> > Is there any way to use regclass without having ERROR?
>>
>> > pgpool-II needs to find the oid from table name and for the purpose it
>> > issues something like "SELECT 'table_name'::regproc::oid". Problem is,
>> > if the table does not exist, an error occured and the transaction
>> > aborts. Ideally if the table does not exist, the SELECT returns 0
>> > (InvalidOid).
>>
>> I don't think the cast should act that way, but I could see providing a
>> separate conversion function that returns 0 ... or perhaps better NULL
>> ... if no match.
>
> Such a function should be very helpfull. Great!
I made pretty simple function for this. Essential part is something
like this:
Datum
pgpool_regclass(PG_FUNCTION_ARGS)
{char *pro_name_or_oid = PG_GETARG_CSTRING(0);Oid result;
PG_TRY();{ result = DirectFunctionCall1(regclassin,
CStringGetDatum(pro_name_or_oid));}PG_CATCH();{ result = InvalidOid;}PG_END_TRY();
PG_RETURN_OID(result);
}
IMO this implementation is the least invasive but not so
elegant.
Before proposing more complete patches, I would like to hear comments:
which way I should go? The least invasive one like above? Or Should I
refactor regclassin, for example implementing "regclassin_gut" which
do the essential job, and making wrapper functions, one is active
existing regclass, and the other act as new one?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
On 04.01.2011 11:07, Tatsuo Ishii wrote:
> Datum
> pgpool_regclass(PG_FUNCTION_ARGS)
> {
> char *pro_name_or_oid = PG_GETARG_CSTRING(0);
> Oid result;
>
> PG_TRY();
> {
> result = DirectFunctionCall1(regclassin,
> CStringGetDatum(pro_name_or_oid));
> }
> PG_CATCH();
> {
> result = InvalidOid;
> }
> PG_END_TRY();
>
> PG_RETURN_OID(result);
> }
>
> IMO this implementation is the least invasive but not so
> elegant.
It's not generally safe to suppress errors like that. You could leak
locks or tuple descriptors etc. And if the error is not "no scuh table",
but e.g. out of memory, you don't want to suppress it anyway.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
> It's not generally safe to suppress errors like that. You could leak > locks or tuple descriptors etc. And if the error is not "no scuh > table", but e.g. out of memory, you don't want to suppress it anyway. Thanks. I will create more "invasive" patch. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Tatsuo Ishii <ishii@postgresql.org> writes:
>> It's not generally safe to suppress errors like that. You could leak
>> locks or tuple descriptors etc. And if the error is not "no scuh
>> table", but e.g. out of memory, you don't want to suppress it anyway.
> Thanks. I will create more "invasive" patch.
Why is any of this necessary? It sure looks like you are solving a
problem at the wrong level.
regards, tom lane
> Why is any of this necessary? It sure looks like you are solving a > problem at the wrong level. Please read upthread. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Tatsuo Ishii <ishii@postgresql.org> writes:
>> Why is any of this necessary? It sure looks like you are solving a
>> problem at the wrong level.
> Please read upthread.
You haven't made any argument why this shouldn't be solvable at the
client side, or at worst with a plpgsql DO block; either of which answer
would have the considerable merit of working against existing server
releases.
But in any case I see no reason to mess with the regclass code.
The C code you want is just
names = stringToQualifiedNameList(class_name);result = RangeVarGetRelid(makeRangeVarFromNameList(names), true);
and there is no way that refactoring is going to yield a solution more
elegant than just duplicating those two lines --- especially since
regclassin has other cases for which zero is a non-error result.
regards, tom lane