Detecting functions installed by an extension
Greetings! How can I tell, using the system catalog or information schema, if a function/procedure was created by an extension as opposed to by the user (i.e. would be dropped if you performed "DROP EXTENSION")? So far I've looked at the `pg_extension` table, which lists the class IDs of configuration tables but doesn't mention procedures. I've looked at `pg_proc` but that doesn't seem to contain the information. I've also scanned over various other system catalogues but with no luck. Is this information available in one of the system catalogs? Does PostgreSQL itself track this information so that it can perform cleanup, or does it expect the extension to clean up after itself? Thank you for your time, Benjie.
Re: Detecting functions installed by an extension
On 17/06/18 10:05, Benjie Gillam wrote: > Greetings! > > How can I tell, using the system catalog or information schema, if a > function/procedure was created by an extension as opposed to by the user > (i.e. would be dropped if you performed "DROP EXTENSION")? So far I've > looked at the `pg_extension` table, which lists the class IDs of > configuration tables but doesn't mention procedures. I've looked at > `pg_proc` but that doesn't seem to contain the information. I've also > scanned over various other system catalogues but with no luck. Is this > information available in one of the system catalogs? Does PostgreSQL > itself track this information so that it can perform cleanup, or does it > expect the extension to clean up after itself? All dependencies are tracked in the system catalog pg_depend. If you do \set ECHO_HIDDEN on in psql and then \dx+ an_extension you can see what queries psql uses to get the information. That should put you well on your way to doing what you want. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Re: Detecting functions installed by an extension
On 17 June 2018 at 10:26, Vik Fearing wrote: > On 17/06/18 10:05, Benjie Gillam wrote: > > Greetings! > > > > How can I tell, using the system catalog or information schema, if a > > function/procedure was created by an extension as opposed to by the user > > (i.e. would be dropped if you performed "DROP EXTENSION")? So far I've > > looked at the `pg_extension` table, which lists the class IDs of > > configuration tables but doesn't mention procedures. I've looked at > > `pg_proc` but that doesn't seem to contain the information. I've also > > scanned over various other system catalogues but with no luck. Is this > > information available in one of the system catalogs? Does PostgreSQL > > itself track this information so that it can perform cleanup, or does it > > expect the extension to clean up after itself? > > All dependencies are tracked in the system catalog pg_depend. > > If you do \set ECHO_HIDDEN on in psql and then \dx+ an_extension you > can see what queries psql uses to get the information. That should put > you well on your way to doing what you want. > -- > Vik Fearing +33 6 46 75 15 36 > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > It seems so obvious in retrospect! This works beautifully - thanks 🙏 Benjie.
Re: Slow planning time for simple query
> "Amit" == Amit Kapila writes: >> Presumably the problem is that the standby isn't authorized to change >> the btree index's "entry is dead" bits, Amit> I don't see anything like that in the code. We use _bt_killitems Amit> to mark the items as dead and neither that function or any of its Amit> caller has any such assumption. See index_fetch_heap: /* * If we scanned a whole HOT chain and found only dead tuples, tell index * AM to kill its entry for that TID (this will take effect in the next * amgettuple call, in index_getnext_tid). We do not do this when in * recovery because it may violate MVCC to do so. See comments in * RelationGetIndexScan(). */ if (!scan->xactStartedInRecovery) scan->kill_prior_tuple = all_dead; (this is the only place where kill_prior_tuple can be set to true) -- Andrew (irc:RhodiumToad)
Re: Trying to understand odd trigger behavior
On Thu, Jun 14, 2018 at 23:04:24 -0500,
Bruno Wolff III wrote:
I have a perl after insert trigger for a table with a non-null column
element and I am getting an occasional error when the trigger executes
for printing a null value which is $TD->{new}{element}. However, I do
the insert with an on conflict that converts it into an update. And I
have a before update trigger that blocks changing the record, but
returns null if the old and new records are the same.
I think I misunderstood the error I was getting. I don't think the value
being printed is null, but rather the socket instance I am trying to
print to. I haven't tested this yet, but it makes sense and my other theory
didn't match later evidence.
Check to see if customer exist in second table. Help needs with psql
psql I have two tables , table A and Table b. Table A has list of about 6 customers ID with all their details and Table B had customer is and age for over 50 rows of customers. I need a query that will take a customer from table a and if that customer exists in table B then insert a yes in a new column in table a. Basically what I want to end up with in table a , is all the customers in this table and if they exist in table B then yes in a column. I.e the final result will be 6 customers and additional column at the end with yes or no depending if they exist in table B. Any help is appreciate it -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Check to see if customer exist in second table. Help needs with psql
On 06/17/2018 12:19 PM, Rv02 wrote: psql I have two tables , table A and Table b. Table A has list of about 6 customers ID with all their details and Table B had customer is and age for over 50 rows of customers. I going to assume you meant Table B has customer ID and age above. Does Table B have duplicate records for customer ID? If so what I offer below might need to be revised. I need a query that will take a customer from table a and if that customer exists in table B then insert a yes in a new column in table a. Basically what I want to end up with in table a , is all the customers in this table and if they exist in table B then yes in a column. I.e the final result will be 6 customers and additional column at the end with yes or no depending if they exist in table B. For future reference there are examples below that cover this: https://www.postgresql.org/docs/10/static/sql-update.html " UPDATE employees SET sales_count = sales_count + 1 FROM accounts WHERE accounts.name = 'Acme Corporation' AND employees.id = accounts.sales_person; " I would test on a dev database or at least use: BEGIN; ... COMMIT; Untested: BEGIN; UPDATE a set new_column = 'f'; UPDATE a SET new_column = 't' FROM b WHERE a.id = b.id; ROLLBACK or COMMIT depending on outcome of above. Any help is appreciate it -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.kla...@aklaver.com
