Postgres sequence problem after DB restore - Help please

gmb · Post by **gmb** » 05.11.2010, 15:20

Hi all,

Using Delphi6 & Postgres with zeos 6.1.5 - and have not had any problems like this before.

My app is very dependant of schemas in the DB and also use serials columns for PK's:
CREATE TABLE sch1.tab1( tabid SERIAL PRIMARY KEY, tabdesc TEXT );
which implies:
CREATE SEQUENCE sch1.tab1_tabid_seq;

My app makes use of TZQuery's APPEND, EDIT, POST methods to insert/update data on the tables - this have worked without any issues in the past.

However, recently I'm encountering errors on the POST method (after APPEND) to the extent of:
1) SQL Error: ERROR: syntax error at or near "sch1" at character 18....
2) SQL Error: ERROR: relation "tab1_tabid_seq" does not exist.

I traced the error: it seems to occur when trying to get the next value in the sequence - seems that the schema is not recognized or taken into account.
This is confirmed: when dropping the SERIAL column and adding it, the error does not re-occur.

When doing the INSERT from other Postgres clients, the data is inserted without problems.

Something else I can add: seems that this is occurring at sites where the database has been restored recently.

Some variables which I can think to contribute this behaviour to:
-recent updates to Postgres 8.3,
-recent updates to Windows 7 (??)
-recent changes in the way Postgres's backup/restore tools handle linking between SERIAL column and a SEQUENCE

Hope somebody can help me out.

P.S. I understand some work with Postgres serials (and sequences) have been revamped in zeos 6.5.1+ and some may suggest I upgrade. The way in which sequences is implemented in newer versions of ZEOS, however, makes it unfeasible for me to redo all my code.

Post by **mdaems** » 08.11.2010, 01:50

Seems like you are not having a zeoslib 6.1.5 problem, but you're trying to do some important steps in 1 go without knowing what's changing.
If you just want to restore a database : just restore it on exactly the same database as the original one. Then your zeoslib 6.1.5 program shoud work as expected. Next step can be upgrading the db or zeoslib. But just one step at a time.

Mark

Wild_Pointer · Post by **Wild_Pointer** » 08.11.2010, 08:55

Hello, gmb,

If I remember correctly, there was a problem with serial collumns once.. In ZDbcPostgreSqlMetadata the expresion pg_get_expr(def.adbin, def.adrelid) should be used instead of adsrc when finding default values for fields. The problem is pg_restore does not restore adsrc correctly. It loses the schema. When I wrote about the problem to postgresql mailing list I was told that I should not use adsrc. I doubt they fixed the backup/restore mechanism. Instead the patch was applied to ZDbcPostgreSqlMetadata. I'm not sure what vertion that was in, but it has been a while...

Good luck!

gmb · Post by **gmb** » 08.11.2010, 13:10

THanks for the replies.

It loses the schema

Hi Wild_Pointer, what you are saying here sounds exactly like my problem. Strange that is only occurring now. Maybe related to changes in pg_dump/pg_restore?

Will changing the ZConnection.Protocol have any impact?
Currently I'm using postgresql-7.3 (for no specific reason - never had any problems so did not think necessary to change it)
although I'm running Postgres 8.3 at most sites (the rest should be PG 8.2).

Wild_Pointer · Post by **Wild_Pointer** » 08.11.2010, 14:42

gmb,

Strange that is only occurring now.

You are truly right.. Have you been doing backup/restore successfully in past? I can't say since when backup/restore cuts the schema off, but I faced the problem more than 2 years ago.

Will changing the ZConnection.Protocol have any impact?

Changing the protocol will not help.
You have 2 options:
1) update zeos components. (that would be the right thing to do). We are now using zeos 7 in our system without problems.
2) have a sql scripts to update pg_attribute.adsrc after restoring the DB (not acceptable in a long run as you would have to have different scripts for different versions ).

Good luck!

gmb · Post by **gmb** » 08.11.2010, 20:54

1) update zeos components. (that would be the right thing to do). We are now using zeos 7 in our system without problems.

As you say this will be the most "correct" way of solving the problem and probably something I'll have to take a look at in the long run.
The introduction of TZSequence in versions after 6.1.5, however, implies a major overhaul of my code - something I just do not have the time for now.
Will have to happen sooner than later, though.

2) have a sql scripts to update pg_attribute.adsrc after restoring the DB (not acceptable in a long run as you would have to have different scripts for different versions ).

Any ideas on how this script will look?

Something else:
I did take a look at ZDbcPostgreSqlMetadata.pas: found that pg_get_expr was only introduced in Zeos 6.6.6;
I do however not get the same type of error with my applications running on zeos 6.5.1. Can this be because of the different way 6.5.1 handles serials (TZSequence)?

BTW After making the necessary changes to the ZDbcPostgreSqlMetadata source (6.1.5) and recompiling it seems to have made the necessary difference.
If all else fails this will have to be my short-term solution

Thanks again for your help

Wild_Pointer · Post by **Wild_Pointer** » 09.11.2010, 08:44

gmb,

Well, the script can be very simple update pg_attrdef set adsrc = pg_get_expr(def.adbin, def.adrelid); Anyway - I have never done so and because only God knows who uses adsrc and for what reasons I would discourage you from updating it (without serious testing at least).

The introduction of TZSequence in versions after 6.1.5, however, implies a major overhaul of my code - something I just do not have the time for now

I use sequences in every table for pseudo key (id) as this saves me alot of time when updating records. I have never used TZSequence yet, so I guess you have nothing to worry about...

BTW After making the necessary changes to the ZDbcPostgreSqlMetadata source (6.1.5) and recompiling it seems to have made the necessary difference

Yes. This is the third solution to your problem I guess...

gmb · Post by **gmb** » 09.11.2010, 09:05

Wild_Pointer
Thanks , I'll probably stick with the 3rd option for now.

The introduction of TZSequence in versions after 6.1.5, however, implies a major overhaul of my code - something I just do not have the time for now
I have never used TZSequence yet

Sorry if this is a stupid question, but how do you manage this?

Using zeos 6.5.1, when I run the following without adding TZSequence and setting ZQuery1's Sequence and Sequencefield properties accordingly, I'll get error: Field 'tabid' must have a value.

Code: Select all

zquery1.sql.text := 'SELECT * FROM sch1.tab1';
zquery1.append;
zquery1.FieldByName('tabdesc').ASString:='foo';
zquery1.post;

Is there another way of doing this where TZSequence is not necessary? That would make upgrading a whole lot easier

Wild_Pointer · Post by **Wild_Pointer** » 09.11.2010, 09:18

gmb,

I don't know what about 6.5.1, but in zeos 7 the management of sequence values is done by handling default value. I just don't insert or update the field with sequence - that is done inside the zeos...

I think your example would run fine with zeos 7.

gmb · Post by **gmb** » 09.11.2010, 10:18

Wild_Pointer
I'll check it out,
Thanks again for your help

gmb · Post by **gmb** » 09.11.2010, 10:23

7.0.0-alpha is the first experimental release which should make zeoslib usable on Delphi 2009 and Delphi 2010. Support for Delphi versions older than Delphi 7 has been removed.

Guess this means I'm stuck with pre ver 7 versions as I'm using delphi6

Post by **mdaems** » 10.11.2010, 23:27

Sorry gmb. You're right.
BUT : no panic, there's no change n sequence handling between 6.6 and 7 as far a I know.

Mark

gmb · Post by **gmb** » 11.11.2010, 10:05

THanks mdaems,
I'll have a look at ver 6.6 then.
Regards