Lookup Primary Key of Foreign Server's Table
I'm looking for a system query that will lookup the primary key column on a
fdw table. It's possible we need to declare that part of the foreign
table's schema in the local (is that the right term?) database?
Here's the foreign table - I don't see anything showing a primary key, so
my hunch is we need to declare it in the local schema?
*=> \d sidecar_link.actions
Foreign table "sidecar_link.actions" Column | Type
| Collation | Nullable | Default
| FDW options
-----------------+-----------------------------+-----------+----------+--------------------------------------------------+-------------
id
| bigint | | not null |
nextval('sidecar_link.actions_id_seq'::regclass) | user_session_id |
bigint | | not null |
| user_id | bigint
| | not null |
| created_at | timestamp without time zone | | not null |
now() | occurred_at |
timestamp without time zone | | not null | now()
| thing_id | integer
| | | |
parent_thing_id | integer | | |
| viewing_id | integer
| | |
| origin | origin |
| | 'mysteryscience'::origin | scope
| text | | not null |
| name | text
| | not null |
| details | text | |
| | request_path
| text | | |
| Server: pg_mysterysci_sidecarFDW options:
(schema_name 'public', table_name 'actions')*
Not really related question, but a curiosity: why does this table not show
in the list of foreign tables?
*=> \det List of foreign tables Schema | Table | Server
--------+-------+--------(0 rows)*
On Tue, Oct 22, 2019 at 9:16 AM Chris Morris <chris@mysteryscience.com>
wrote:
I'm looking for a system query that will lookup the primary key column on
a fdw table. It's possible we need to declare that part of the foreign
table's schema in the local (is that the right term?) database?Here's the foreign table - I don't see anything showing a primary key, so
my hunch is we need to declare it in the local schema?*=> \d sidecar_link.actions*
I'm not seeing anything in the local FDW table definition that deals with
PK/FK constraints so I'm led to believe that what you are asking is not
possible. Consider detailing what your goal is and not just your technical
need.
Not really related question, but a curiosity: why does this table not show
in the list of foreign tables?
I suspect for the same reason you added the schema prefix "sidecar_link" to
the table name when you issued \d above.
*=> \det List of foreign tables Schema | Table | Server
--------+-------+--------(0 rows)*
David J.
The foreign table has a primary key. Ruby on Rails uses a system query to
lookup what the primary key on the table is, but it's querying the local
database, and not finding anything. In a schema dump of the local database,
I don't see a primary key defined, so I'm presuming I need to issue an ADD
CONSTRAINT command against the foreign table to reflect what is actually
true in the foreign table? Is that correct?
On Wed, Oct 23, 2019 at 8:47 AM Chris Morris <chris@mysteryscience.com>
wrote:
The foreign table has a primary key. Ruby on Rails uses a system query to
lookup what the primary key on the table is, but it's querying the local
database, and not finding anything. In a schema dump of the local database,
I don't see a primary key defined, so I'm presuming I need to issue an ADD
CONSTRAINT command against the foreign table to reflect what is actually
true in the foreign table? Is that correct?
The documentation says doing what you suggest won't work...
David J.
Yeah. Part of my confusion is due to Heroku providing a Data Links service
that handles a lot of the internal details around establishing a
foreign server connection, and not knowing exactly what to expect.
I experimented with IMPORT FOREIGN SCHEMA in a couple of test databases and
noticed that there was no declaration of primary key brought over to the
local database. So, this is something ActiveRecord simply won't be able to
handle right now, and I have to take care of that myself, which is easy
enough to do.
Thx for the feedback.
On Wed, Oct 23, 2019 at 11:12 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Wed, Oct 23, 2019 at 8:47 AM Chris Morris <chris@mysteryscience.com>
wrote:The foreign table has a primary key. Ruby on Rails uses a system query to
lookup what the primary key on the table is, but it's querying the local
database, and not finding anything. In a schema dump of the local database,
I don't see a primary key defined, so I'm presuming I need to issue an ADD
CONSTRAINT command against the foreign table to reflect what is actually
true in the foreign table? Is that correct?The documentation says doing what you suggest won't work...
David J.