Tools to convert timestamp data to another time zone in PostgreSQL

[Joel Rabinovitch]

Hi,

We have recently modified our application to work with PostgreSQL databases and 
schemas. We also support Oracle and SQL Server Databases.

Along with adding support for PostgreSQL, we have upgraded our infrastructure 
such that all environments are configured to use the UTC time zone. Previously, 
the environments were configured to use the time zone where the database server 
and application server were installed.

As a result, we have hit an issue where we need to convert data in timestamp 
columns in existing records to reflect that the time is in UTC. The timezone is 
not specified in our timestamp columns (i.e. they are defined as timezone 
without time zone). We need to do this for interoperability between the 
database engines we support.

After a bit of searching, we found we can write an SQL similar to the one below 
to do the conversion:

update client
   set create_stamp = (create_stamp at time zone 'America/New_York' at time 
zone 'UTC')
where client_code = 'HOANA';

This does work correctly. However, we have some limitations in terms using SQL 
statements like this.

- We would need to identify the timestamp columns that would be affected across 
many tables and multiple schemas.
- We also store date-only information in timestamp without time zone columns. 
This was done as a result of migrating our application from Oracle where the 
DATE data type was used at the time (Oracle now supports timestamp columns).

I was wondering if you are aware of any open source and/or commercial tools 
that could allow us to easily identify the affected columns, exclude columns if 
necessary, and apply the necessary conversion. If not, we would have to write a 
utility that does this for us, which could be a lengthy process.

Thanks,

Joel

RE: Migrating PostgreSQL Stored Procedures to MSSQL 2019 for example

[Joel Rabinovitch]

Hi,

When we did our migration from SQL Server to PostgreSQL, we mainly used two 
tools:

sqlserver2pgsql for most of our schema-related changes. You can find that here: 
https://github.com/dalibo/sqlserver2pgsql.
AWS Schema Conversion Tool (SCT) for migrating views and stored 
functions/procedures. The AWS SCT also can do schema conversions. You can find 
that here: https://aws.amazon.com/dms/schema-conversion-tool/

For the AWS SCT tool, you don't really need to use AWS schemas. You can install 
PostgreSQL locally, and save the SQL files it generates and then apply them 
yourself. Not sure if that's a violation of their license terms though (our 
company does use AWS).

Keep in mind that these conversion tools do the majority of the work, but the 
output they generate needs to be reviewed. For example, columns with an 
isjson() constraint need to be converted to json or jsonb columns. Stored 
procedures/functions sometimes don't convert at all.

The following sites also provide the ability to conversion SQL statements using 
SQL Server-specific syntax to PostgreSQL.

https://www.jooq.org/translate/
https://www.sqlines.com/online
https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

Hope this helps,

Joel

From: Scott Simpson 
Sent: Friday, August 12, 2022 6:17 AM
To: pgsql-general@lists.postgresql.org
Subject: Migrating PostgreSQL Stored Procedures to MSSQL 2019 for example

ATTENTION: This email originated from outside of Tecsys. Use caution when 
clicking links or opening attachments. | Ce courriel provient de l'extérieur de 
Tecsys. Soyez prudent lorsque vous cliquez sur des liens ou ouvrez des pièces 
jointes.

Hi,

I need to migrate many PostgreSQL Stored Procedures and functions to MSSQL.

I can find anything online that seems to handle this task.

Are there any tools that you have that can do this job?



Kind Regards



Zellis | Scott Simpson | Senior Engineer



[cid:image001.png@01D8AE1E.AC41B970]

Thorpe Park

United Kingdom

Work : +44 (0)20 3986 3523

Email : scott.simp...@zellis.com

Web : 
www.Zellis.com





Zellis is the trading name for Zellis Holdings Ltd and its associated companies 
"Zellis".

The contents of this email are confidential to Zellis and are solely for the 
use of the intended recipient. If you received this email in error, please 
inform the sender immediately and delete the email from your system. Unless 
Zellis have given you express permission to do so, please do not disclose, 
distribute or copy the contents of this email.

Unless this email expressly states that it is a contractual offer or 
acceptance, it is not sent with the intention of creating a legal relationship 
and does not constitute an offer or acceptance which could give rise to a 
contract.

Any views expressed in this email are those of the individual sender unless the 
email specifically states them to be the views of Zellis.

Zellis Holdings Ltd - registered in England and Wales - Company No: 10975623 - 
Registered Office: 740 Waterside Drive, Aztec West, Almondsbury, Bristol, BS32 
4UF, UK.

Questions on PostgreSQL 13.4 Installer for Windows

[Joel Rabinovitch]

Hi,

Our developers do their work using a local PostgreSQL database server and local 
schemas.

This is typically done by installing the Windows version of the PostgreSQL 
installer from EnterpriseDB. Currently, version 13.4 (64-bit) of the installer 
is being used.

Recently, our organization has installed BeyondTrust Privilege Management 
software on our developer's PCs. The software forces the developer to 
authenticate when doing administrative tasks such as installing software and 
adjusting environment variables.

When a developer attempts to install the PostgreSQL 13.4 database server, the 
developer is authenticated and the software runs as an administrator.

The issue occurs when the installer initializes the database cluster. When it 
attempts to do this,  it requires that it be done as a non-administrator.

This causes the installation process to fail. We have a manual procedure to 
follow when this occurs, which involves manually initializing the database 
cluster as a non-administrator, installing the Windows service as an 
administrator and reinstalling some of the PostgreSQL components.

The process works, but it is quite cumbersome.

Has anyone had a similar issue when using the PostgreSQL installer for Windows 
when the BeyondTrust Privilege Management (or similar software) is installed on 
their PC. If so, what have you done to resolve this.

We have been in contact with the company that develops the BeyondTrust 
software, but they have not been able to help us correct the problem as of yet.

Thanks,

Joel

RE: How to add function schema in search_path in option definitio

[Joel Rabinovitch]

Hi,

We have hit similar issues with the software that we develop. We don’t specify 
the schema names in stored procedures/functions we create.

The way we resolved it was to define the schemas where the stored 
procedures/functions are located in the search path as part of the connection 
string we use. In our case, we connect using JDBC, so the search path is 
defined in the currentSchema argument.

We needed to do this because one of the things our installation teams do is 
copy schemas used in one environment, such as a production environment, to 
another environment,  such as a test environment. When that is done, the 
schemas are renamed as per our installation standards. If we hardcoded the 
schema names in the stored procedures/functions, the installer would have to 
manually adjust the schema names used.

Thanks,

Joel

From: David G. Johnston 
Sent: Tuesday, July 11, 2023 4:51 PM
To: Lorusso Domenico 
Cc: Adrian Klaver ; 
pgsql-general@lists.postgresql.org
Subject: Re: How to add function schema in search_path in option definitio

ATTENTION: This email originated from outside of Tecsys. Use caution when 
clicking links or opening attachments. | Ce courriel provient de l'extérieur de 
Tecsys. Soyez prudent lorsque vous cliquez sur des liens ou ouvrez des pièces 
jointes.

On Sat, Jul 8, 2023 at 10:00 AM Lorusso Domenico 
mailto:domenico@gmail.com>> wrote:
Hello Adrian,
I've created a schema to handle some specific features.
In the schema there are a couple of tables used by many functions (more than 
20).
In other words, I've created a schema as a package (as suggested in many 
points).

I wish, in a function of this schema, to be able to call each other functions 
of this schema without adding the schema name in the call instruction.

PostgreSQL isn't really designed for that - especially if you aren't formally 
creating an extension but simply putting stuff into a schema.

The script code you use to install your makeshift package should handle dynamic 
schema naming.  It's a pain, do you really need to allow the name of the schema 
to be unknown at installation time?

You should read up on how extensions are implemented - you are basically 
writing your own CREATE EXTENSION implementation.

https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION

In any case, there really aren't any smarts here: explicitly schema qualify 
your function calls and forget that search_path even exists.  Unless you are 
writing custom operators, and even then, consider search_path to be evil.

David J.

Performance question about using autosave=always and cleanupSavepoints=true

[Joel Rabinovitch]

Hi,

Currently, our application supports SQL Server databases and Oracle schemas. We 
are updating our application to support PostgreSQL schemas. We are using 
version 13.4 of PostgreSQL.

Our application is written in Java and connects to PostgreSQL schemas using 
JDBC.

In our framework, we have logic that attempts to retry an SQL statement when a 
row is locked. It does this up to 999 times before it gives up.

When this logic is connected using PostgreSQL schemas, we receive error 
messages similar to the ones below:

org.postgresql.util.PSQLException: ERROR: could not obtain lock on row in 
relation "my_table"

org.postgresql.util.PSQLException: ERROR: current transaction is aborted, 
commands ignored until end of transaction block

A Google search led to the following StackOverflow post:

hxxps://stackoverflow.com/questions/10399727/psqlexception-current-transaction-is-aborted-commands-ignored-until-end-of-tra
 (Replace hxxps by https to access it).

which indicates that PostgreSQL refuses to execute valid SQL statements on the 
same connection after an invalid SQL statement is executed.

To get around this, we have added the following arguments to the JDBC 
connection string:

autosave=always&cleanupSavepoints=true

This resolves the problem, but the concern we are having is in terms of 
performance.

Based on the following link:

hxxps://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/
 (Replace hxxps by https to access it), it indicates that using this flag can 
have a serious impact on performance. However, it doesn't really provide an 
alternative way to get around it.

In terms of real-world experience, what are the disadvantages of using the 
autosave and cleanupSavepoints arguments?

Would it be better to manually set the savepoint in our application code and 
then rollback if there is an error. This would probably have to be done for 
each attempt we try to lock a record, so I am not sure if there is any 
advantage of doing this.

Thanks,

Joel