8000 Latest changes broke several databases on different servers · Issue #40 · dataegret/pgcompacttable · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Latest changes broke several databases on different servers #40

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
xalt7x opened this issue Sep 22, 2021 · 8 comments
Open

Latest changes broke several databases on different servers #40

xalt7x opened this issue Sep 22, 2021 · 8 comments

Comments

@xalt7x
Copy link
xalt7x commented Sep 22, 2021 •
edited
Loading

I used pgcompacttable on Windows with PSQL versions 9.4 and 11 for a long time. Everything went smooth with version from 2020-09-01
Yesterday I tried updated version (2021-09-10) and got few broken databases on different servers.
ERROR: could not open relation with oid
I have log from only one server. At first glance it doesn't have anything interesting, no errors. On a second server log had few "SQL errors" which never occurred before.

Please revert latest changes.
Unfortunately I'm unable to help with further testing and most likely would just use previous version.
Thanks.
@Melkij
Copy link
Collaborator
Melkij commented Sep 22, 2021

I need more details.

could not open relation with oid

It doesn't seem to be related to anything that pgcompacttable can do.

@xalt7x
Copy link
Author
xalt7x commented Sep 22, 2021 •
edited
Loading

That could't be concupiscence because happened on 2 different servers. Both databases broke exactly after pgcompacttable. One of them was restored with REINDEX in PGAdmin4.

@Melkij
Copy link
Collaborator
Melkij commented Sep 22, 2021 •
edited
Loading

Details are still needed.

ERROR: could not open relation with oid

Which exactly OID? This is a low level postgresql error. pgcompacttable doesn't do anything at such a low level. We use high-level regular SQL commands only.
In response to what actions does the DBMS return this error?

few "SQL errors"

Which exactly?

@xalt7x
Copy link
Author
xalt7x commented Sep 22, 2021 •
edited
Loading

"ERROR: could not open relation with oid" is from an accounting program 1c: Enterprise. It showed during log-in. I don't have any more information except log from server where database was restored using REINDEX.

Log with "SQL error" from a server where I was unable to restore database, is unavailable right now. Few tables where referenced (like they already exist or moved, I can't recall exact phrase).

@Melkij
Copy link
Collaborator
Melkij commented Sep 22, 2021

Well known, hmm, thing, let's call it that. So you are using a fork and not vanilla postgresql? This definitely needs to be mentioned.

In normal PostgreSQL, the error message "could not open relation with oid" contains the exact number. The database server log will also contain text of the query which gives this error.

< 8000 /p>

@xalt7x
Copy link
Author
xalt7x commented Sep 22, 2021 •
edited
Loading

If that's useful there is OID number on a broken server
ERROR: could not open relation with OID 30181568

On restored server I still have PSQL log and it points to different OID. There's some personal information so I would like to share only essential parts of log (lt has a lot of repeated lines anyway)

< 2021-09-21 18:00:14.298 EEST >ERROR: could not open relation with OID 530497
< 2021-09-21 18:00:14.298 EEST >STATEMENT: SELECT
'\000\000\0011'::bytea,
T1._IDRRef
FROM _Document305 T1
WHERE ((T1._Fld723 = CAST(0 AS NUMERIC))) AND ((T1._Date_Time >= '2021-03-05 00:00:00'::timestamp) AND (T1._Date_Time <= '2021-12-29 23:59:59'::timestamp) AND (T1._Fld7844 = '161'::mvarchar))
UNION ALL SELECT
'\000\000H\314'::bytea,
T2._IDRRef
FROM _Document18636 T2
WHERE ((T2._Fld723 = CAST(0 AS NUMERIC))) AND ((T2._Date_Time >= '2021-03-05 00:00:00'::timestamp) AND (T2._Date_Time <= '2021-12-29 23:59:59'::timestamp) AND (T2._Fld18685 = '161'::mvarchar))
< 2021-09-21 18:00:14.303 EEST >WARNING: there is no transaction in progress

@xalt7x
Copy link
Author
xalt7x commented Sep 22, 2021 •
edited
Loading

So I tried previous version 1.0.10 (2020-09-01) with restored (using REINDEX) database
It was launched with --force switch.
pgcompacttable output a lot of errors similar to what I've seen on a different server

[Wed Sep 22 20:56:20 2021] (database_name) It is recommended to set ionice -c 3 for pgcompacttable: ionice -c 3 -p 6340
[Wed Sep 22 20:56:22 2021] (database_name) Handling tables. Attempt 1
[Wed Sep 22 20:57:21 2021] (database_name:public._accumrg15387) SQL Error: ERROR: type "_accumrg15387_bydims15401_rtrn" already exists
[Wed Sep 22 20:57:21 2021] (database_name:public._accumrg15387)
[Wed Sep 22 20:57:21 2021] (database_name:public._accumrg15693) SQL Error: ERROR: relation "pgcompact_index_5336" already exists
[Wed Sep 22 20:57:21 2021] (database_name:public._accumrg15693) SQL Error: ERROR: type "_accumrg15693_byrecorder_rn" already exists
[Wed Sep 22 20:57:21 2021] (database_name:public._accumrg15693)

@xalt7x
Copy link
Author
xalt7x commented Sep 23, 2021

So I tried again version 1.0.10 (2020-09-01) on the proper database (restored from backup) and one again it worked perfectly. Guess I'll just stick with that version for a while...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@Melkij @xalt7x
0