PostgreSQL transaction on two schemas

[FCS]

Hello,

I have data stored in tables on public schema and blob data stored in the table "blob" on "blob01" schema. Tables on public schema have constrains to blob table.
I have one transaction from the uniconnection.
I receive error when I try store data inside one transaction into both schemas.

Should I create separate transactions for each schemas ?

Regards
Michal

[FCS]

Hello,

I have found the source of this problem.

The problem occurs after restore blob data using pg_restore. The pg_backup/pg_restore procedure changes the schema name of sequence in nextval.

I have two schemas: public and blob01.
In blob01 schema I have only one table "blob" with primary key ID defined as:

Code: Select all

 nextval('blob01.osr_blob_id_seq'::regclass) 

After restoring by PG_restore the definition is changed to:

Code: Select all

 nextval('osr_blob_id_seq'::regclass) 

the schema name blob01 in definition is dropped. Due to this the sequence is not accessible because it is not in public schema. The sequence is in its original location in blob01 schema.

Due to this the transaction is broken.

To solve this situation I add blob01 schema into search_path by:

Code: Select all

 ALTER DATABASE my_database SET search_path TO "$user",public,blob01; 

In my opinion it is workaround not solution.

Have you any idea how to proper solve this problem with pg_restore/pg_dump?

Regards
Michal

[azyk]

In order to backup or restore tables that are in different schemes, the search_path variable must contain the names of all these schemes separated by commas. More details about using search_path can be found in the PostgreSQL documentation: http://www.postgresql.org/docs/9.1/stat ... lient.html .

To avoid the workaround you have found:

ALTER DATABASE my_database SET search_path TO "$user",public,blob01;

you should specify all the scheme names separated by comma in the TUniConnection.SpecificOptions.Values['Schema'] property value before connection to the server. For example:

Code: Select all

UniConnection.SpecificOptions.Values['Schema'] := 'public,blob01';

[FCS]

Hello,

Thanks for your reply. I didn't know that I could use the SpecificOptions to put schemas names.

I understand, that the "Alter Database" is good too.

Regards
Michal

[azyk]

Yes, both these solutions set the search_path variable value, but there are differences:
When specifying the schema list via

ALTER DATABASE my_database SET search_path TO "$user",public,blob01;

the solution will affect all the connections to the my_database database. While using the TUniConnection.SpecificOptions.Values['Schema'] property will affect only the current connection and won't affect the others.

[FCS]

Hello,

Thanks for your reply.

Could you tell me what will happen if I will have the blob tables in each schema:

public.blob - first table
public.blob.id = nextval('osr_blob_id_seq'::regclass) - field

blob01.blob - second table
blob01.blob.id = nextval('blob01.osr_blob_id_seq'::regclass) - field

What will happen after dump/restore ?

As I understand the
blob01.blob.id = nextval('blob01.osr_blob_id_seq'::regclass)
will be changed to
blob01.blob.id = nextval('osr_blob_id_seq'::regclass)

This way the public.osr_blob_id_seq will be called instead of blob01.osr_blob_id_seq ?

I ask you, because you are the expert of databases. This is the theoretical problem but important as well.

Regards
Michal

[azyk]

This question is about PostgreSQL specific behavior for your specified conditions and is not related to UniDAC. Please refer to the PostgreSQL documentation or forward your question to PostgreSQL developers.

[FCS]

Hello,

Thanks for your reply.

I had reported this problem on the postgreSQL forum, but they answered me that this was proper behaviour of pg_dump/pg_restore procedure, and I should set the search_path to the schemas.

In my opinion this is a bug, because pg_dump/pg_restore changes the database definition.

Regards
Michal