1

I have a Postgres database that stores all of the information collected by our system and then a schema called playback that contains a small portion of the tables contained in the public schema. This playback schema is created using an automated script that is given the few needed views/tables in the schema and then it finds all of the tables that are depended on and creates them in the schema. (This answer shows how I retrieve the dependencies)

Some of these tables are currently auto-populated using rules and this means that there is an entry in the pg_depends table that can be queried. We are currently converting some of these rules to triggers that call functions, because when large chunks of the data are copied into the playback schema the currval('some_sequence') doesn't work properly. (This answer also suggested that this is the proper way to do it)

Unfortunately, this means that there is no longer an entry in pg_depends and the automated script that creates the schema is leaving out some tables. Is there a way for me to retrieve similar dependency information from the functions/triggers? Or is there a better way to do this in general?

Community
  • 1
  • 1
Dave Johansen
  • 889
  • 1
  • 7
  • 23

2 Answers2

1

So you want to know what tables are used in the function called by a trigger. That's not possible. See under Halting Problem for the reason. In current versions, PostgreSQL doesn't make an attempt to track this, even if it might be possible in some cases (language SQL functions come to mind, but you can't write triggers in that anyway). In fact, using a function is semi-officially recommended as a way to circumvent the dependency system.

In your application, you should probably look for a way to track these dependencies yourself.

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90
  • I'm writing the functions in PL\pgSQL and it's unfortunate that there's not a mechanism to extract the information from the generated function, but I guess I will just come up with another solution to track this information external to the database. – Dave Johansen Feb 21 '11 at 16:41
0

So you are looking for a way to get the functions used by a trigger?

SELECT tgfoid FROM pg_trigger WHERE oid = %u

or

SELECT tgfoid FROM pg_trigger WHERE tgrelid = '%s'::regclass AND tgname = '%s'

and so on. Depends what information you have available.

There are also records about this in pg_depend.

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90