Re: [HACKERS] regclass without error?
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 is...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
Tatsuo Ishii is...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
Tatsuo Ishii is...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
On Fri, Sep 3, 2010 at 4:28 AM, Tatsuo Ishii is...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
Tatsuo Ishii is...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regclass without error?
Tatsuo Ishii is...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
