Thread: Column type changed "spontanously"?
I have several columns that were created as "timestamp without time zone", but I changed them in 2014 to "timestamp with time zone". Recently, when I got notified that times had suddenly changed, I checked and found the columns had reverted to "timestamp without time zone." This seems impossible, yet it seems to have happened. Any ideas on what could cause this? My application has the privileges to do this, as it changed the data type to support time zones. But there is no code that could change it back not to support time zones.
The database in on
"PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit"
What other information would help solve this?
Chuck Martin
Avondale Software
On Thursday, May 9, 2019, Chuck Martin <clmartin@theombudsman.com> wrote:
I have several columns that were created as "timestamp without time zone", but I changed them in 2014 to "timestamp with time zone". Recently, when I got notified that times had suddenly changed, I checked and found the columns had reverted to "timestamp without time zone." This seems impossible, yet it seems to have happened. Any ideas on what could cause this? My application has the privileges to do this, as it changed the data type to support time zones. But there is no code that could change it back not to support time zones.
No clue
The database in on"PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit"
Given the issues with the early 9.3 releases running 9.3.1 is a extremely unwise decision. Beyond the unwise-ness of not keeping up with minor releases generally.
What other information would help solve this?
Log files containing DDL commands
David J.
On 5/9/19 6:14 AM, Chuck Martin wrote: > I have several columns that were created as "timestamp without time > zone", but I changed them in 2014 to "timestamp with time zone". > Recently, when I got notified that times had suddenly changed, I checked > and found the columns had reverted to "timestamp without time zone." > This seems impossible, yet it seems to have happened. Any ideas on what > could cause this? My application has the privileges to do this, as it > changed the data type to support time zones. But there is no code that > could change it back not to support time zones. > > The database in on > > "PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) > 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit" > > What other information would help solve this? Assuming you have log_statement set to ddl or higher and the logs still exist I would look in them for an ALTER TABLE ... statement on that column. > > Chuck Martin > Avondale Software -- Adrian Klaver adrian.klaver@aklaver.com
On 5/9/19 6:14 AM, Chuck Martin wrote: > I have several columns that were created as "timestamp without time > zone", but I changed them in 2014 to "timestamp with time zone". > Recently, when I got notified that times had suddenly changed, I checked > and found the columns had reverted to "timestamp without time zone." > This seems impossible, yet it seems to have happened. Any ideas on what > could cause this? My application has the privileges to do this, as it > changed the data type to support time zones. But there is no code that > could change it back not to support time zones. > > The database in on > > "PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) > 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit" > > What other information would help solve this? Following up on David's observation on updating minor releases, I came across this from the release notes for 9.3.3: " Avoid multiple name lookups during table and index DDL (Robert Haas, Andres Freund) If the name lookups come to different conclusions due to concurrent activity, we might perform some parts of the DDL on a different table than other parts. At least in the case of CREATE INDEX, this can be used to cause the permissions checks to be performed against a different table than the index creation, allowing for a privilege escalation attack. (CVE-2014-0062) " The commit notes: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5f173040e324f6c2eebb90d86cf1b0cdb5890f0a > > Chuck Martin > Avondale Software -- Adrian Klaver adrian.klaver@aklaver.com