Re: 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(at)postgresql(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

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(at)gf(dot)microolap(dot)com

Tatsuo Ishii <ishii(at)postgresql(dot)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

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(at)commandprompt(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

> Tatsuo Ishii <ishii(at)postgresql(dot)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(at)postgresql(dot)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(at)postgresql(dot)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(at)postgresql(dot)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