1
select parse_ident('abc,efi&xyz');

Will stay there for ever, not returning. But a similar command will finish.

select regexp_split_to_array('abc,efi&xyz', '[,&]');
 regexp_split_to_array 
-----------------------
 {abc,efi,xyz}
(1 row)

Not sure what's wrong with my database. I am running a vacuum full analyze at the moment. Not sure that cause the hang.

2
  • The parse_ident(qualified_identifier) is used to split qualified_identifier into an array of identifiers, removing any quoting of individual identifiers.
    – Jacky
    Jan 17, 2019 at 20:40
  • @Kemin: I am not sure what you are asking. Please check the active process to define which processes are locking your vacuum.
    – Jacky
    Jan 17, 2019 at 20:44

2 Answers 2

2

You're losing error messages,

# select parse_ident('abc,efi&xyz');
ERROR:  string is not a valid identifier: "abc,efi&xyz"

They also do different things:

  • parse_ident takes a string a fully-qualified identifier -- something you can use to identify a thing in a database, and returns the breakdown of it in an array. For example, if you had a column named abc,efi&xyz on a table named table in a schema named schema in a database named db, you could do,

    SELECT parse_ident('"abc,efi&xyz".table.schema.db');
               parse_ident           
    ---------------------------------
     {"abc,efi&xyz",table,schema,db}
    (1 row)
    

    From this point you could use array addressing to identify the table, (parse_ident(...)[2]).

  • regexp_split_to_array takes any string and simply splits it returning an array. Unlike parse_ident it doesn't require a fully-qualified identifier, and it doesn't understand ANSI-quotes inside the identifier (as I used in my example above).

1

I figured it out: I was running DbSchema program that is locking a lot of tables including the meta tables.

After terminating DbSchema, things started moving. I figured it out when I tried to 'drop table tmp;', and it also got hung. After terminating DbSchema, the drop table finished. Then the parse_ident() started to work. For some reason, DbSchema is locking the database even the meta data.

lbwf3=# select parse_ident('abc,efi&xyz');
ERROR:  string is not a valid identifier: "abc,efi&xyz"
2
  • 1
    You should report that as a bug for DbSchema - locking up a the system tables like that is an extremely nasty thing to do. I can't even think of a way how that could happen "accidently". Jan 18, 2019 at 6:54
  • DbSchema (shell script around a java program) is a reasonable affordable product, not worth the time. For many versions, the product got some shell script error and cash, each time after automatic update I have to fix it manually. I think finally they get it right. I send my fix to them several time; reported the bug several times.
    – Kemin Zhou
    Jan 19, 2019 at 5:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.