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

Search Discussions

  • Robert Haas at Sep 3, 2010 at 1:33 pm

    On Fri, Sep 3, 2010 at 4:28 AM, Tatsuo Ishii 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
  • Tatsuo Ishii at Sep 3, 2010 at 1:46 pm

    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
  • Marko Tiikkaja at Sep 3, 2010 at 2:11 pm

    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
  • David Fetter at Sep 3, 2010 at 2:21 pm

    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
  • Alvaro Herrera at Sep 3, 2010 at 5:28 pm

    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
  • David Fetter at Sep 3, 2010 at 5:31 pm

    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
  • Alvaro Herrera at Sep 3, 2010 at 5:44 pm

    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
  • Tom Lane at Sep 3, 2010 at 2:31 pm

    Tatsuo Ishii 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
  • David E. Wheeler at Sep 3, 2010 at 4:02 pm

    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
  • Tatsuo Ishii at Sep 3, 2010 at 11:44 pm

    Tatsuo Ishii 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
  • Tatsuo Ishii at Jan 4, 2011 at 9:07 am
    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
  • Heikki Linnakangas at Jan 4, 2011 at 11:44 am

    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
  • Tatsuo Ishii at Jan 4, 2011 at 12:08 pm

    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
  • Tom Lane at Jan 4, 2011 at 3:40 pm

    Tatsuo Ishii 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
  • Tatsuo Ishii at Jan 4, 2011 at 3:48 pm

    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
  • Tom Lane at Jan 4, 2011 at 4:01 pm

    Tatsuo Ishii 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
  • Pavel Golub at Sep 3, 2010 at 2:36 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 3, '10 at 8:28a
activeJan 4, '11 at 4:01p
posts18
users9
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase