After spending pretty much the entire week on freeing a customer from the chains of Oracle's license policy, I found an interesting issue, which made me smile: When the Oracle code was built 15 years ago or so, somebody created a table called “pg_type” on the Oracle side as part of the application. With only Oracle in mind, this is absolutely fine. However, now, 15 years and thousands of installations later, this thing has to be ported to PostgreSQL without renaming existing tables. Remember, there are thousands of installations in the field and most of them have no remote connection or are simple in scary locations (Iraq, Afghanistan, etc.).
So, here is some pg_type related trickery, which might be entertaining or maybe even enlightening:
|
1 2 |
test=# CREATE TABLE pg_type (id int); CREATE TABLE |
The first observation is that a table called “pg_type” can easily be created on the PostgreSQL side.
Let us see, what happens, when we try to read the table:
|
1 2 3 4 5 |
test=# SELECT count(*) FROM pg_type; count ------- 1879 (1 row) |
Well, this does not seem to be what we wanted. The reason is: In PostgreSQL there is a system table carrying the same name. It stores information about data types and all that. Logically system tables are always taken into consideration first, so in our case we see more than just an empty new table.
To solve that the table has to be explicitly prefixed:
|
1 2 3 4 5 |
test=# SELECT count(*) FROM public.pg_type; count ------- 0 (1 row) |
Without prefixing, even d is empty:
|
1 2 |
test=# \d No relations found. |
In PostgreSQL there is a thing called search_path, which allows you to tell the system, what to look for where. The interesting part is that you can actually add the pg_catalog schema to the search_path to control things:
|
1 2 3 4 5 6 7 8 |
test=# SET search_path TO public, pg_catalog; SET test=# SELECT count(*) FROM pg_type; count ------- 0 (1 row) |
Adding pg_catalog after public elegantly solves the problem.
If you want to stay away from trickery, just renaming the table still is an option …
In case you need any assistance, please feel free to contact us.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
This post talks about using pg_ctl to register PostgreSQL as a service on Windows. In my experience many big PostgreSQL instances are on Linux / UNIX these days. However, recently we have seen quite a number of Windows systems running PostgreSQL for a bigger production system. There are still rarely really large databases (XX TB) on Windows. However, Windows might very well catch up a bit in the future.
On Linux there are ready to use packages, which make it very easy to deploy more than just one instance on a single node.
On Windows the story is a bit different. I have seen many people, who had a hard time registering services. To all of those out there suffering in silence there is a hopefully vital piece of information...
If you happen to run Windows, pg_ctl provides a nice way to register a service:
|
1 2 |
pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-S a[uto] | d[emand] ] [-w] [-t seconds] [-s] [-o options] |
Using “pg_ctl register” is a lot easier than to fiddle around with Windows command line interfaces in general. It allows people to quickly register services in a nice and clean way.
Read on to find out more about PostgreSQL and Windows on Pavlo's blog detailling install and setup for PostgreSQL on WSL2 for Windows.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
